Filtering With Or Logical Operator on Embedded Resources Inconsistent Behavior #3349
-
EnvironmentDefault Supabase Description of issueI am having trouble getting filtering to work on embedded resources while keeping the embedded resources expanded in the response. I have the following tables:
The filter I am trying to create is the following: 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 UpdateI 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") |
Beta Was this translation helpful? Give feedback.
Replies: 2 comments 2 replies
-
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.
If you only need the .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 Now, what you need is to get an embedded resource that is not
.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 ( |
Beta Was this translation helpful? Give feedback.
-
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 referencehttps://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.
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
Then instead of querying like this: 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? |
Beta Was this translation helpful? Give feedback.
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.
If you only need the
matches
then your first query is enough, as you mentioned. Now, when you ask for the data insiderequesting_boxer
andreceiving_boxer
here: