OR-Filtering in resource embedding

[MHC2000]

Environment

• PostgreSQL version: 13
• PostgREST version: 12.01
• Operating system: linux

Description of issue

Hello,

I want to use the general or filter für emedded resources.
For example dt_1 is the main table, dt_1_lt_8 is connected to this table by a key (1:n).
In a query I want to filter over several columns of dt_1 or dt_1_lt_8 if there is somthing named 'admin'
My first Idea was to filter it like that
dt_1?select=id,email,department,lt_8:dt_1_lt_8!user2user_group_user_fkey(user_group:dt_1_ot_9!user2user_group_user_group_fkey(id,label,title))&or=(lt_8.user_group.label.ilike(any).{*admin*},email.ilike(any).{*admin*},department.ilike(any).{*admin*})

But getting an error:

{
  "code": "PGRST100",
  "details": "unexpected \"u\" expecting \"not\" or operator (eq, gt, ...)",
  "hint": null,
  "message": "\"failed to parse logic tree ((lt_8.user_group.label.ilike(any).{*admin*},email.ilike(any).{*admin*},department.ilike(any).{*admin*}))\" (line 1, column 9)"
}

So the embedded resources are not available in the or-Operator

If I'm filtering directly lt_8 by: dt_1?select=id,email,department,lt_8:dt_1_lt_8!user2user_group_user_fkey(user_group:dt_1_ot_9!user2user_group_user_group_fkey(id,label,title))&or=(email.ilike(any).{*admin*},department.ilike(any).{*admin*})&lt_8.user_group.label=ilike(any).{*admin*}

Obviously I'm only getting the values where one of the ors and the label of user_group in lt_8 matches.

My question is: Is it possible to filter embedded resources by an or with the main table columns or do I miss something?

Regards,

Manfred

[laurenceisla]

You need to do null filtering on embedded resources to allow or across embedded tables.

The or operator only groups by columns in the same level, it cannot nest embedded resources, unless you use not.is.null for the embedding. In other words, in your first example, you need to separate the lt_8 filter in a separate query string and add lt_8.not.is.null to the or filter. Your second example is closer to what you want but you were missing the null filter inside the or, like this:

or=(email.ilike(any).{*admin*},department.ilike(any).{*admin*},lt_8.not.is.null)&lt_8.user_group.label=ilike(any).{*admin*}

This syntax it's a bit inconvenient, so a better alternative is being discussed in issue #2563.

[MHC2000]

@laurenceisla thanks for the hint. After a while I figured to get it work. As you said, not really convenient and straightforward, but it works.