Filtering on Id?

[milosloub]

Do you know, how should be handled filtering like this?
api/clients?filter[id]=in:1,2,3

this leads to make all resources derive from "BaseEntity" with Identifiable override:

    [Attr("id")]
    public override int Id { get => base.Id; set => base.Id = value; }

it's ok, but then id is presented in top-level and in attributes also. Do you know
how to handle this as the best practice?

[jaredcnance]

Originally, id and relationships were excluded from filtering because there were better ways to get those objects (i.e. routing /foo/:id). And the other filters (like, lt, gt, etc.) did not make sense for ids and relationship keys.

But, as our filtering feature set has expanded, there are now possible queries that cannot be expressed through routing, but might be useful to application developers (e.g. in, nin, isnotnull, isnull, etc).

In general I think we should expand filtering to support both id and relationship keys.

The workaround you've presented is probably the best way to do it ATM.

[milosloub]

I can make solution to globally allow something like this:
resources?filter[id]=1

And relationship id filter like following:
1) resources?filter[relationship]=1

OR some automatic expose {Relationship}Id as [Attr]:
2) resources?filter[relationshipId]=1

OR:
3) resources?filter[relationship.id]=1

From the json:api docs https://jsonapi.org/recommendations/#filtering it should be option 1)
but it seems to me confused when it comes to distinguishing when filter[something] is either an attribute or a relationship. Solution 2) doesn't seems good to me, but I had this also in my mind. Solution 3) can be quite satisfying, but there should include provided. What do you think about this?

[jaredcnance]

@milosloub my thought is that when building the ResourceGraph we could just add Id to the list of attributes here:

JsonApiDotNetCore/src/JsonApiDotNetCore/Builders/ContextGraphBuilder.cs

Line 134 in 3ade2a7

protected virtual List<AttrAttribute> GetAttributes(Type entityType)

Then I think that would make the following just work:

• resources?filter[id]=1
• resources?filter[relationship.id]=1 (like you mentioned, we may have to do something special here so we don't have to include the resources)

[milosloub]

@jaredcnance this implementation looks quite easy. I did small test with relationships in repository layer:

        public override IQueryable<Resource> Get()
        {
            return base.Get()
                .Where(i => i.Relationship.Id == 1);
        }

and this is translated to sql as WHERE RelationshipId = 1, so no include is required.
This allow to resolve [HasOne] use case.

What about [HasMany]? Should be allowed something like:
[GET] api/resource?filter[items.id]=1
Or should it be requested to api/items itself?

Current implementation throws exception, when user try to filter nested attribute on collection relationship.
From my point of view it's not necessary to deal with [HasMany], but it just confuses me. Do you know the right direction of this?

[jaredcnance]

Okay, that's great! I didn't realize EF was smart enough to do that 😝. I'll need to think more about the HasMany case, but for now I think the HasOne solution should be sufficient and we can iterate if there is demand.

Initially, I think a query that filters on HasMany (e.g. /authors?filter[articles.id]=1) should actually just be restructured (e.g. /articles/1?include=author) since there really isn't a way for us to execute this query without a JOIN. I recognize this isn't perfect, but may be satisfactory.

[milosloub]

I was also surprised 😄 I'll prepare PR in few days with some tests