Filtering With Or Logical Operator on Embedded Resources Inconsistent Behavior

[liamcsdev97]

Environment

Default Supabase

Description of issue

I am having trouble getting filtering to work on embedded resources while keeping the embedded resources expanded in the response.

I have the following tables:

Match {
    requesting_boxer: ForeignKey -> Boxer,
    receiving_boxer: ForeignKey -> Boxer,
}

Boxer {
    first_name: text,
    last_name: text,
}

The filter I am trying to create is the following:
select all matches where the requesting boxer's first name is 'Abigail' or the receiving boxer's last name is 'Carrol'

I am using the following query (this is using the rust postgrest client):

.from("matches")
.select("*,requesting_boxer(),receiving_boxer()")
.eq("requesting_boxer.first_name", "Abigail")
.eq("receiving_boxer.last_name", "Carroll")            
.or("requesting_boxer.not.is.null,receiving_boxer.not.is.null")

This query works as I expect and returns the correct matches, however the requesting_boxer and receiving_boxer columns are not embedded and instead only have the foreign key.

Modifying the query to the following does not embed the resources:

.from("matches")          
.select("*,requesting_boxer(),receiving_boxer()")           
.eq("requesting_boxer.first_name", "Abigail")           
.eq("receiving_boxer.last_name", "Carroll")      
.or("requesting_boxer.not.is.null,receiving_boxer.not.is.null")            
.select("*,requesting_boxer!inner(*),receiving_boxer!inner(*)")

I'm assuming this is expected behavior.

However, if I update the query to the following, I can embed the resources but the result is problematic

.from("matches")            
.select("*,requesting_boxer(*),receiving_boxer(*)")            
.eq("requesting_boxer.first_name", "Abigail")            
.eq("receiving_boxer.last_name", "Carroll")            
.or("requesting_boxer.not.is.null,receiving_boxer.not.is.null")

I receive the same rows, but now the requesting_boxer and receiving_boxer columns can be null and this causes my deserialization to fail since both columns are required to exist.

If I update my query to the following, I don't get the same rows

.from("matches")
.select("*,requesting_boxer!inner(*),receiving_boxer!inner(*)")
.eq("requesting_boxer.first_name", "Abigail")
.eq("receiving_boxer.last_name", "Carroll")            
.or("requesting_boxer.not.is.null,receiving_boxer.not.is.null")

Since !inner is basically equivalent to the not.is.null, the or conditional is irrelevant and the query uses an & condition instead. So now I don't get the same rows.

And finally, the following is not valid syntax:

.from("matches")            
.select("*,requesting_boxer!inner(*),receiving_boxer!inner(*)")            
.or("requesting_boxer.first_name.not.eq.Abigail,receiving_boxer.last_name.eq.Carrol")

Anytime you try to filter on an embedded field you get a syntax error since the parser expects an operator after the first '.'

How am I supposed to filter embedded resources using the or operator while still embedding the resource in the result? This has been really frustrating to work out given how simple the query is.

The section of the docs is what I was referencing: https://postgrest.org/en/v11/references/api/resource_embedding.html#null-filtering-on-embedded-resources

Update

I was able to achieve proper filtering by selecting the column into an alias and filtering on the alias. Not sure if this is the proper solution or a workaround.
This is what my query looks like:

.from("matches")          
.select("*,requesting_boxer!inner(*),receiving_boxer!inner(*),req:requesting_boxer(),rec:receiving_boxer()")           
.eq("req.first_name", "Abigail")           
.eq("rec.last_name", "Carroll")      
.or("req.not.is.null,rec.not.is.null")        

[laurenceisla]

I wouldn't call it "inconsistent" behavior, since similar queries using different resources will work in the same way. But I get that it can become confusing when filters across embeddings are used.

The filter I am trying to create is the following:
select all matches where the requesting boxer's first name is 'Abigail' or the receiving boxer's last name is 'Carrol'

If you only need the matches then your first query is enough, as you mentioned. Now, when you ask for the data inside requesting_boxer and receiving_boxer here:

.from("matches")            
.select("*,requesting_boxer(*),receiving_boxer(*)")            
.eq("requesting_boxer.first_name", "Abigail")            
.eq("receiving_boxer.last_name", "Carroll")            
.or("requesting_boxer.not.is.null,receiving_boxer.not.is.null")

Note that .or("requesting_boxer.not.is.null,receiving_boxer.not.is.null"), admits that either one, requesting_boxer or receiving_boxer, can be null. So it gives the expected result, with the null boxers if the .eq condition is not met.

Now, what you need is to get an embedded resource that is not null, so it is impossible to get with just the embeds used to filter the resource...

I was able to achieve proper filtering by selecting the column into an alias and filtering on the alias. Not sure if this is the proper solution or a workaround.

.from("matches")          
.select("*,requesting_boxer!inner(*),receiving_boxer!inner(*),req:requesting_boxer(),rec:receiving_boxer()")           
.eq("req.first_name", "Abigail")           
.eq("rec.last_name", "Carroll")      
.or("req.not.is.null,rec.not.is.null")        

... So this is the correct solution. Which is, basically, to filter the top level resource (matches) using filters across resources, and then adding embeds to the already filtered top resource.

[liamcsdev97]

Not sure if this is still being watched. If not I will post a new discussion. I've been experimenting with computed relationships. I'm wondering if it's possible to compute a relationship with a single column instead of an entire row.

Example reference

https://postgrest.org/en/v12/references/api/resource_embedding.html#computed-relationships

In this case a function is created to embbed films inside the premieres row.

create foreign table premieres (
  id integer,
  location text,
  "date" date,
  film_id integer
) server import_csv options ( filename '/tmp/directors.csv', format 'csv');

create function film(premieres) returns setof films rows 1 as $$
  select * from films where id = $1.film_id
$$ stable language sql;

However, you have to create this function for each table that you want to support embedding for. Is it possible instead to create a function that accepts a single unnamed parameter to instead compute the relationship?

Example reference

https://postgrest.org/en/v12/references/api/stored_procedures.html#functions-with-a-single-unnamed-parameter

CREATE FUNCTION get_film(integer) RETURNS setof film rows 1 AS $$
  SELECT * from film where film.id = $1
$$ LANGUAGE SQL;

Then instead of querying like this: premieres?select=*,film(id)
Do something like this: premieres?select=*,get_film(film_id,(id, name, ...))

I'm not sure if this is currently possible; I haven't been able to find any similar examples in the documentation, but I think it would make things much easier to work with since it doesn't require the use of foreign keys and can be used across muliple definitions as long as the parameter is specified. I'm sure the syntax of my example would have to change a bit to disambiguate it from the currently implemented resource embedding, but since the function is required to be a single unnamed parameter, i think it would be possible to add.

I am far from an expert in sql and postgres so this may be more difficult than I am suggesting, but is it possible to add this feature or is it already possible to do something like this?

[laurenceisla]

Hmm... I understand the idea of "reusing" the function. But a "computed" relationship uses the table (a composite type) as the only parameter of the function, similar to how "computed fields" work.

but I think it would make things much easier to work with since it doesn't require the use of foreign keys

That is also true for Computed Relationships, you do not require to specify foreign keys between the resources. You can build whatever you want inside the function as long as the table is present as a single parameter. In the first example, I could do something like this and the relationship would still work:

create function film(premieres) returns setof films rows 1 as $$
  select * from films; -- not using premieres at all and returning all films
$$ stable language sql;

All in all, I think it's easy enough to copy paste the function and replace the parameter, if you need to use it for other resources.

[laurenceisla]

However, you have to create this function for each table that you want to support embedding for.

Just to clarify, this is only true if the tables do not have FK relationships between them or if they are foreign tables, for instance. Otherwise, PostgREST will detect the embedding according to the foreign keys.