Can't check for two relations in same where clause

REST
1

I’d like to be able to find conversations (saved in a table) where both the current user and the user whose account page they are looking at are participants. I have a 1:N relation column in the conversations table called participants and I’m trying to use the where clause participants.objectId = ‘{user1Id}’ AND participants.objectId = ‘{user2Id}’. When user1Id and user2Id are the same (ie. the person is looking at their own account page) the database returns the conversations with that person. When user1Id and user2Id are different it returns an empty array even if they are participants in the same conversation. Is there a way to construct a where clause that searches for two different objects in the same relation column and get only results that include both?

2

Hello, @Jacki_Saorsail

If I understood you correctly, to achieve the desired result, you can first retrieve conversations where user1Id is a participant, After that, you can filter the resulting array to get only those conversations where user2Id is also a participant.

Regards,
Serhiy

3

Makes sense, but could mean I retrieve a very large array to find only one or two they have in common. Is there a way to do that in a transaction?

I ended up checking if user 1 is the owner and user 2 a participant and vice versa. That way it doesn’t retrieve every group forum they both belong to which wouldn’t be relevant in this context, just PMs one of them started. Not perfect, but good enough.