1:N property and whereClause question

General
1

Hello! I have question: suppose i have a table called Conversations which have 1:N property ‘persons’ to table Users. I need to get all conversations with userIds which i know.

I tried whereClause:
“persons.objectId = ‘id1’ and persons.objectId = ‘id2’”, or
“persons.objectId IN (‘id1’, ‘id2’)”, but its return empty array.

Thanks.

2

Hi @Dmitry_Kalenkov

Try like this:
persons.objectId = "id1" OR persons.objectId = "id2"

Regards,
Marina

3

Hello @Marina.Kan
Thanks for your answer, but it is not i’m looking for. In your case we get ALL conversations where persons with id1 or id2 are take part. But my goal is get conversations where and id1 and id2 simultaneously.

4

Hello @Dmitry_Kalenkov,

This case cannot be done using just where clause.

E.g. if you have

conversation1: userId1, userId2
conversation2: userId2, userId3

and you want to get only conversations where userId1 and userId2 are present (conversation1), you should do the next retrieval:

Screenshot 2023-04-20 at 15.50.17
Screenshot 2023-04-20 at 15.50.171244×644 79.4 KB

in this retrieval whereClause looks like

persons.objectId='userId1' OR persons.objectId='userId2'

Please notice the count(persons.objectId) as countPerson property which is used in having clause, so we filter the conversations where both users are present (if you want to get conversations where 3 users are present, the having condition will be countPerson > 2 etc).

1 Like
5

Hello @olhadanylova. Your way is working nice!
But I can’t actually realize why for “conversation2: userId2, userId3” in this case return countPerson = 1, if we have two userIds.

6

Because of the whereClause which looks like
persons.objectId='userId1' OR persons.objectId='userId2'
Only userId2 fits that condition, userId3 - doesn’t.

1 Like