postgrest

Postgrest filter does not seem work with fields from related table

I would like to retrieve records of kiscourse where the related joblist.job has a value that contains tech within the joblist.job string value.

This returns the expected results:

/joblist?select=job,kiscourse_id(*)&limit=10&job=ilike.*tech*

This does not:

/kiscourse?select=*,joblist(*)&limit=10&joblist.job=ilike.*tech*

And according to: https://postgrest.com/en/v4.1/api.html#embedded-filters-and-order, this seems to be the intended:

> GET /films?select=*,roles(*)&roles.character=in.Chico,Harpo,Groucho
> HTTP/1.1

Once again, this restricts the roles included to certain characters but does not filter the films in any way. Films without any of those characters would be included along with empty character lists.

Is there any way to accomplish the above (besides procedures)?

Solution

  • Postgrest now supports top-level filtering by adding the !inner flag to the select query.

    From the docs:

    In order to filter the top level rows you need to add !inner to the embedded resource. For instance, to get only the films that have an actor named Jehanne:

    GET /films?select=title,actors!inner(first_name,last_name)&actors.first_name=eq.Jehanne HTTP/1.1