Postgrest filter does not seem work with fields from related table

Question

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)?

score 2 · Answer 1 · answered Jul 07 '17 at 16:45

2

the joblist.job filter you have there will affect only the entities on the second level, it does not apply to the first level.

The way to read this query /kiscourse?select=*,joblist(*)&limit=10&joblist.job=ilike.*tech* is this:

Give me 10 rows from kiscourse with all the columns, and for each row, i want the joblists for that row that are named *tech*

answered Jul 07 '17 at 16:45

Ruslan Talpa

533
3
8

2

Is there a way to accomplish this though? – Chris Stryczynski Jul 07 '17 at 16:48
Start from the other end with `/joblist` as top level. – Ruslan Talpa Jul 07 '17 at 16:50
The other options are a view that does the join or a stored procedure – Ruslan Talpa Jul 07 '17 at 16:51
Ohhh a view with a join. Yes that would work perfectly! – Chris Stryczynski Jul 07 '17 at 16:53
Actually it wouldn't as there are joblist is a many to one relationship... So the results would have potential duplicates. – Chris Stryczynski Jul 07 '17 at 17:01

Postgrest filter does not seem work with fields from related table

1 Answers1