How to use legacy filter to get nested result

[anand2122]

SUMMARY

We are trying to filter with an included object using a legacy filter giving up errors

DETAILS

Hi,

We are trying to filter with an included object using a legacy filter (e.g., exp:), but we are not getting the expected results. We are receiving the following error:

"errors": [
{
"id": "e1112795-e822-4cab-8d94-b37075cb924d",
"status": "400",
"title": "The specified filter is invalid.",
"detail": "Field chain on resource type 'authors' failed to match the pattern: zero or more relationships, followed by a to-many relationship. Relationship on resource type 'books' expected. Failed at position 14: filter[books.^desc]",
"source": {
"parameter": "filter[books.desc]"
}
}
]

Also sometime getting this error as well

"detail": "This query string parameter can only be used on a collection of resources (not on a single resource)."

The normal filter works with the following URL:

https://localhost:7070/api/Authors?include=books&filter[books]=equals(desc,'pqr')

However, the legacy filter is causing issues:

https://localhost:7070/api/Authors?include=books&filter[books.desc]=expr:eq:1

`public class Book : Identifiable
{
[Key]
[Column("Id")]
public override int Id { get => base.Id; set => base.Id = value; }
[Attr(PublicName = "title")]
public string Title { get; set; }

  [HasOne(PublicName = "author")]
   public Author Author { get; set; }
  public int AuthorId { get; set; }

  [Attr]
  public string Desc { get; set; }

}

public class Author : Identifiable
{
[Key]
[Column("Id")]
public override int Id { get => base.Id; set => base.Id = value; }
[Attr] public string Name { get; set; }
[HasMany] public List Books { get; set; }
}
`

API Response: I would need included filter response - "included":

{ "links": { "self": "https://localhost:7070/api/Authors?include=books", "first": "https://localhost:7070/api/Authors?include=books" }, "data": [ { "type": "authors", "id": "1", "attributes": { "name": "J.K. Rowling" }, "relationships": { "books": { "links": { "self": "https://localhost:7070/api/authors/1/relationships/books", "related": "https://localhost:7070/api/authors/1/books" }, "data": [ { "type": "books", "id": "1" }, { "type": "books", "id": "2" } ] } }, "links": { "self": "https://localhost:7070/api/authors/1" } }, { "type": "authors", "id": "2", "attributes": { "name": "George R.R. Martin" }, "relationships": { "books": { "links": { "self": "https://localhost:7070/api/authors/2/relationships/books", "related": "https://localhost:7070/api/authors/2/books" }, "data": [ { "type": "books", "id": "3" }, { "type": "books", "id": "4" } ] } }, "links": { "self": "https://localhost:7070/api/authors/2" } }, { "type": "authors", "id": "3", "attributes": { "name": "J.R.R. Tolkien" }, "relationships": { "books": { "links": { "self": "https://localhost:7070/api/authors/3/relationships/books", "related": "https://localhost:7070/api/authors/3/books" }, "data": [ { "type": "books", "id": "5" }, { "type": "books", "id": "6" } ] } }, "links": { "self": "https://localhost:7070/api/authors/3" } } ], "included": [ { "type": "books", "id": "1", "attributes": { "title": "Harry Potter and the Philosopher's Stone", "desc": "abc" }, "relationships": { "author": { "links": { "self": "https://localhost:7070/api/books/1/relationships/author", "related": "https://localhost:7070/api/books/1/author" } } }, "links": { "self": "https://localhost:7070/api/books/1" } }, { "type": "books", "id": "2", "attributes": { "title": "Harry Potter and the Chamber of Secrets", "desc": "pqr" }, "relationships": { "author": { "links": { "self": "https://localhost:7070/api/books/2/relationships/author", "related": "https://localhost:7070/api/books/2/author" } } }, "links": { "self": "https://localhost:7070/api/books/2" } }, { "type": "books", "id": "3", "attributes": { "title": "A Game of Thrones", "desc": "sss" }, "relationships": { "author": { "links": { "self": "https://localhost:7070/api/books/3/relationships/author", "related": "https://localhost:7070/api/books/3/author" } } }, "links": { "self": "https://localhost:7070/api/books/3" } }, { "type": "books", "id": "4", "attributes": { "title": "A Clash of Kings", "desc": "The notes" }, "relationships": { "author": { "links": { "self": "https://localhost:7070/api/books/4/relationships/author", "related": "https://localhost:7070/api/books/4/author" } } }, "links": { "self": "https://localhost:7070/api/books/4" } }, { "type": "books", "id": "5", "attributes": { "title": "The Hobbit", "desc": null }, "relationships": { "author": { "links": { "self": "https://localhost:7070/api/books/5/relationships/author", "related": "https://localhost:7070/api/books/5/author" } } }, "links": { "self": "https://localhost:7070/api/books/5" } }, { "type": "books", "id": "6", "attributes": { "title": "The Lord of the Rings", "desc": null }, "relationships": { "author": { "links": { "self": "https://localhost:7070/api/books/6/relationships/author", "related": "https://localhost:7070/api/books/6/author" } } }, "links": { "self": "https://localhost:7070/api/books/6" } } ] }

STEPS TO REPRODUCE

Postman request: https://localhost:7070/api/Authors?include=books&filter[books.desc]=expr:eq:1

VERSIONS USED

• JsonApiDotNetCore version: JsonApiDotNetCore (5.6.0)
• ASP.NET Core version: 8
• Entity Framework Core version:
• Database provider:

[bkoelman]

Legacy filters are always applied to the resource type being requested (so authors in /api/authors?include=books), there is no universal syntax to filter in related resources. This is the primary reason we introduced our own syntax. This is documented at https://www.jsonapi.net/usage/reading/filtering.html#legacy-filters:

The next section describes how filtering worked in versions prior to v4.0. They are always applied on the set of resources being requested (no nesting).

The expr: prefix can be used to specify modern syntax (which offers more operators) inside a legacy filter, but it still only gets applied to the resource being requested. To filter in related resources, use modern syntax instead.

Is there any reason you want to keep using legacy syntax?

[bkoelman]

The core problem is this: https://localhost:7070/api/Authors?include=books&filter[books.desc]=eq:some could mean:

1. Give me only authors whose books contain a description with value "some"
2. Give me all authors, but filter the related books per author

The syntax is ambiguous.

[anand2122]

@bkoelmanFirst of all, I appreciate your response. Here are the points:

We need authors whose books contain a description with the value "abc".
Provide specific authors and their included books based on the filter as shown in the example below.

Database Screenshot

Steps to Access the Filter - https://localhost:7070/api/Authors/1?include=books

Step 1: Access authors having id = 1.

Step 2: Include books that either have id = 1 or desc = 'abc'.

[anand2122]

I just tried with filter like this - https://localhost:7070/api/Authors/1?include=books&filter[books.desc]=eq:abc

Giving me error like this

"errors": [ { "id": "1e120e19-d6a9-495c-b168-c1ee81cb77ef", "status": "400", "title": "The specified filter is invalid.", "detail": "Field chain on resource type 'authors' failed to match the pattern: zero or more relationships, followed by a to-many relationship. Relationship on resource type 'books' expected. Failed at position 14: filter[books.^desc]", "source": { "parameter": "filter[books.desc]" } }

[bkoelman]

Why do you keep trying with legacy filter syntax? I already explained it does not work like that.

Sounds like you need two filters:

• filter authors that have at least one related book with a specific description
• filter the related books that have a specific description

That would be: /api/authors?filter=has(books,equals(desc,'some'))&filter[books]=equals(desc,'some')

If that's not what you need, please provide sample records for both tables and the expected response.

[bkoelman]

@anand2122 Does this answer your question? Do you need this issue to remain open?