Multiple top-level filter conditions on the same embedded resource

[SpiritQuaddicted]

https://docs.postgrest.org/en/stable/references/api/resource_embedding.html#top-level-filtering explains how to: get only the films that have an actor named Jehanne (as first_name).

As I am struggling getting queries that reference the same embedded resource multiple times to work, I think having more examples would help:

1. How can I get only the films that have an actor with first_name Jehanne OR an actor with first_name John?
2. How can I get only the films that have an actor with first_name Jehanne AND an actor with first_name John?
3. How can I get only the films that have an actor with first_name Jehanne AND (an actor with first_name John OR an actor with first_name Samuel)?
4. How can I get only the films that have an actor with first_name Jehanne OR an actor with last_name d'Alcy?

---

In reality I have a slightly different database setup but I think these examples would help others and certainly me.

I have the equivalent of films(id, title) and actors(id, first_name, last_name, film_id), so a more direct relationship without an additional "link table" like roles, but I thought it would be useful to stay in the official example. If that different structure changes things though, I can of course provide more info.

Thank you!

[wolfgangwalther]

1. How can I get only the films that have an actor with first_name Jehanne OR an actor with first_name John?

You have multiple options here, but all the same idea:

• /films?select=title,actors!inner(first_name,last_name)&actors.first_name=in.Jehanne,John
• /films?select=title,actors!inner(first_name,last_name)&or=(actors.first_name.eq.Jehanne,actors.first_name.eq.John)

2. How can I get only the films that have an actor with first_name Jehanne AND an actor with first_name John?

You need multiple embeddings for that case. Same as in SQL, where you'd need multiple JOINs or EXISTS subqueries.

• /films?select=title,jehanne:actors!inner(first_name,last_name),john:actors!inner(first_name,last_name)&jehanne.first_name=eq.Jehanne&john.first_name=eq.John

3. How can I get only the films that have an actor with first_name Jehanne AND (an actor with first_name John OR an actor with first_name Samuel)?

Putting the first two together, that's simple:

• /films?select=title,jehanne:actors!inner(first_name,last_name),john_or_samuel:actors!inner(first_name,last_name)&jehanne.first_name=eq.Jehanne&john_or_samuel.first_name=in.John,Samuel

4. How can I get only the films that have an actor with first_name Jehanne OR an actor with last_name d'Alcy?

Tiny adjustment from the first case:

• /films?select=title,actors!inner(first_name,last_name)&or=(actors.first_name.eq.Jehanne,actors.last_name.eq.d'Alcy)

I think the step that is hard to take mentally is that you'll need to add multiple embeddings in some cases. Especially, when you consider the case "give me only the films with Jehanne as an actor, but return all actors for that film". Then you'll need one !inner to filter the rows and one without !inner to get the list of all actors.

[SpiritQuaddicted]

Thank you very much!

I tried to adapt this to my structure but failed with parsing errors. Then I tried to simply copy and paste the query above against my postgrest and still got similar parsing errors (no complaints about the films or actors tables not actually existing). Should those queries throw parsing errors if I don't have the tables or is something else broken with my setup?

Running PostgREST 12.2.3 on PostgreSQL 17.0.

/films?select=title,actors!inner(first_name,last_name)&actors.first_name=in.Jehanne,John

{"code":"PGRST100","details":"unexpected \"J\" expecting \"(\"","hint":null,"message":"\"failed to parse filter (in.Jehanne,John)\" (line 1, column 4)"}

/films?select=title,actors!inner(first_name,last_name)&or=(actors.first_name.eq.Jehanne,actors.first_name.eq.John)

{"code":"PGRST100","details":"unexpected \"f\" expecting \"not\" or operator (eq, gt, ...)","hint":null,"message":"\"failed to parse logic tree ((actors.first_name.eq.Jehanne,actors.first_name.eq.John))\" (line 1, column 11)"}

• /films?select=title,jehanne:actors!inner(first_name,last_name),john_or_samuel:actors!inner(first_name,last_name)&jehanne.first_name=eq.Jehanne&john_or_samuel.first_name=in.John,Samuel

{"code":"PGRST100","details":"unexpected \"J\" expecting \"(\"","hint":null,"message":"\"failed to parse filter (in.John,Samuel)\" (line 1, column 4)"}

• /films?select=title,actors!inner(first_name,last_name)&or=(actors.first_name.eq.Jehanne,actors.last_name.eq.dAlcy)

{"code":"PGRST100","details":"unexpected \"f\" expecting \"not\" or operator (eq, gt, ...)","hint":null,"message":"\"failed to parse logic tree ((actors.first_name.eq.Jehanne,actors.last_name.eq.dAlcy))\" (line 1, column 11)"}

(I removed the "'" from this one to be able to easily execute it with curl --globoff 'url' in my shell.)

---

Sorry for not testing with actual tables. I will actually build a minimal reprocudible example if this parsing step is actually influenced by the database schema. To me it sounds like it one step before that?

[wolfgangwalther]

I wrote this out of my head without testing them on any schema. They might have syntax errors, you better double check with the docs for each specific thing. This was just meant to give you an idea about the concepts behind it.

[wolfgangwalther]

For example the in operator is missing parentheses, apparently: https://docs.postgrest.org/en/v12/references/api/tables_views.html#operators

[wolfgangwalther]

Also the or seems to be written slightly different according to https://docs.postgrest.org/en/v12/references/api/resource_embedding.html#embedded-filters:

Should be more something like actors.or=(first_name.eq.Jehanne,first_name.eq.John).

[SpiritQuaddicted]

Thank you!

I tried to fix the examples and I think I was successful as it now (rightfully) complains with {"code":"PGRST200","details":"Searched for a foreign key relationship between 'films' and 'actors' in the schema 'public', but no matches were found.","hint":null,"message":"Could not find a relationship between 'films' and 'actors' in the schema cache"} on my database where those tables don't exist. No parsing errors anymore :)

1. These seem to work:

• /films?select=title,actors!inner(first_name,last_name)&actors.first_name=in.(Jehanne,John)'
• /films?select=title,actors!inner(first_name,last_name)&actors.or=(first_name.eq.Jehanne,first_name.eq.John)

3. This seems to work:

• /films?select=title,jehanne:actors!inner(first_name,last_name),john_or_samuel:actors!inner(first_name,last_name)&jehanne.first_name=eq.Jehanne&john_or_samuel.first_name=in.(John,Samuel)

4. This seems to work:

• /films?select=title,actors!inner(first_name,last_name)&actors.or=(first_name.eq.Jehanne,last_name.eq.dAlcy)

Is the data behind the examples available? Then I would set it up and test with the actual data.

[wolfgangwalther]

Is the data behind the examples available? Then I would set it up and test with the actual data.

I'm not sure whether the exact tables are available in our test data, but it might be worth a try. You'd have to see how to set up the development environment and how to use postgrest-run in nix-shell, though.