OR with a mix of root and embedded resources

[joachimlucas]

Hi
I have this query:
/suppliers?select=code,name,types(*)&types.or=(label.ilike.*myword*,description.ilike.*myword*)
It works well ;-)
But I don't understand how to add "code" to the or= condition ?
I tried :
/suppliers?select=code,name,types(*)&or=(code.ilike.*myword*,types.label.ilike.*myword*,types.description.ilike.*myword*)
But the parser doesn't understand types.label.ilike.
Is there a solution to chain OR with resources and embedded resources?
THANKS

[laurenceisla]

Hello! To do an OR across embedded resources you need to use not.is.null for the resources inside an or. But, in your case, since you want to filter the top resource suppliers with an embedded resource, you don't need to use not.is.null for the top one. In other words, in your first example, to add an OR code = %myword% to the types.or= filter, you need to do this:

/suppliers?select=code,name,types(*)&types.or=(label.ilike.*myword*,description.ilike.*myword*)&or=(code.ilike.*myword*,types.not.is.null)

We added &or=(code.ilike.*myword*,types.not.is.null). This means that PostgREST will get all the suppliers that have code.ilike.*myword* OR those where the embedded types is not null, that is, the ones that have the filters types.or=(...).

Try it out and let us know if it worked.

[joachimlucas]

It works perfectly !
thank you very much Laurenceisla

[joachimlucas]

Hello
I thought the problem was resolved but if the searched string is found in the code, the returned resource contains types: null even though it is not null. if the searched string is found in types.label for example, types in returned resource is not null as expected.

The OR filters work but the returned resource does not contain all the data while the SELECT specifies them.
With your explanation, I now understand the OR filter with not.is.null but I don't understand the mechanism at work for the non select in returned resource ...
thanks for your help

[laurenceisla]

... if the searched string is found in the code, the returned resource contains types: null even though it is not null.

Ah, if I'm not mistaken, then you want to show the types when the or=(code.ilike.*myword*,...) is fulfilled, regardless if the types do not satisfy the filter: types.or=(label.ilike.*myword*,description.ilike.*myword*)?

The resource embedding will only show the types that fulfill that filter, that's why it returns null. In order to get what you want, you need to use two embeddings: an empty one to filter the top level query, and another to embed the relevant data, types(),types(*). In your example, you need to use an alias, e.g. t:types(), to filter the correct embedding:

/suppliers?select=code,name,t:types(),types(*)&t.or=(label.ilike.*myword*,description.ilike.*myword*)&or=(code.ilike.*myword*,t.not.is.null)

[joachimlucas]

Thank you very much Laurence !
Your solution works wonderfully 👍