Or filtering across embedded resources query construction

[shania-g]

I have two PostgreSQL tables.

Table A:

Column	Type
id	Primary key
user_id	Foreign key (user.id)
title	Varchar

Table B:

Column	Type
id	Primary Key, Foreign key (a.id)
user_id	Primary key, Foreign key (user.id)

This is a one to many relationship, so there is always one entry in table A which has none, one, or multiple matches in table B.

I now need to construct a PostgREST query that gives me all the results when there is an entry for a specified user_id in the Table A or the Table B.

How can I do this? I figured I need https://docs.postgrest.org/en/stable/references/api/resource_embedding.html#or-filtering-across-embedded-resources since I need to join two tables together and then look if either the main resource (Table A) contains that user_id, or the embedded resource (Table B). But I do not quite understand how I would construct this query now. What I have tried so far, which does not work:

http://localhost:3000/a?select=*,b(*)&or=(user_id.eq.8d084598-9a33-4766-ab97-b27d86c69759,b.cs.[{"user_id":"8d084598-9a33-4766-ab97-b27d86c69759"}])

This gives the error "Column a.b does not exist", since I somehow need the or filtering across embedded resources I guess.

[laurenceisla]

How can I do this?

You need to use b.not.is.null inside the or and filter the b table outside of it. Something like this:

curl 'http://localhost:3000/a?select=*,b(*)&b.user_id=eq.8d084598-9a33-4766-ab97-b27d86c69759&or=(user_id.eq.8d084598-9a33-4766-ab97-b27d86c69759,b.not.is.null)'

It could be read like this: "Get data from the a table, where the user_id is 8d084598-9a33-4766-ab97-b27d86c69759 or where the filter for the b embed is not null".

As a follow up, you may want to get the data from the b table when the user_id is not equal to 8d084598-9a33-4766-ab97-b27d86c69759. The next part of the documentation mentions this, you would need one empty embed to filter and another to retrieve data:

curl 'http://localhost:3000/a?select=*,b(*),bf:b()&bf.user_id=eq.8d084598-9a33-4766-ab97-b27d86c69759&or=(user_id.eq.8d084598-9a33-4766-ab97-b27d86c69759,bf.not.is.null)'