Where Clause with multiple relations?

General
1

I have a table that has a 1:many relationship to another table. If I have a query with one conditional for the relation’s objectId - I get a result:

tags.objectId like ‘%97E09DE7-7ABA-460E-B18E-FA38C2BCD72A%’

If I add a second like clause, I get no results:

tags.objectId like ‘%97E09DE7-7ABA-460E-B18E-FA38C2BCD72A%’ AND tags.objectId like ‘%9050C02F-0165-43E2-8D1E-74740FEB19DF%’

I confirmed in the data browser that one record has both of these relations.

Based on other forum posts, it looks like I need run the query with one condition and ‘load relations’ turned on. Then iterate through the results to filter for additional relations.

Please confirm this is the only way and can’t be done via query. Thanks!

3

Hi @Brian_Le_Roy ,

Unfortunately you will not be able to construct such query due to the way in which underlying SQL query constructed.

Based on other forum posts, it looks like I need run the query with one condition and ‘load relations’ turned on. Then iterate through the results to filter for additional relations.

This is the only way in which you can implement required object loading.

More technical details:
When you add condition based on 1:N relation, server will construct query with join. This query will produce Cartesian product on which your condition will be applied. In your case with 1:N relation, searched object will have two separate entries in Cartesian product - one for 97E09DE7-7ABA-460E-B18E-FA38C2BCD72A and another for 9050C02F-0165-43E2-8D1E-74740FEB19DF. So when your where clause is applied no records of this Cartesian product can be found because there are two records for searched object but resulting where clause expects a single entry with these two IDs.

Regards, Andriy

1 Like
4

Thanks for the thorough response!

1 Like