Query room sqlite checking two columns

Question

I have a conversation table like so

Conversation

+----+-----------+--------------+
| Id | sender_id | recipient_id |
+----+-----------+--------------+
|  0 |         3 |            2 |
|  1 |         2 |            1 |
|  2 |         1 |            3 |
+----+-----------+--------------+

So basically, a sender_id could be in the recipient_id column depending on anyone that initiates a conversation first.

How do I make a query, for instance passing a recipient_id, 2. And check if it is in sender_id column, or recipient_id column. Something like:

@Query("select * from conversation where sender_id = :userId OR recipient_id = :userId  ")
LiveData<Conversation> getAConversationByUserId(long userId);

Will this work? Haven't tried it. should I use || or OR?

Answer 1

you could try the following:

    SELECT * 
    FROM conversation
    WHERE sender_id = user_id OR recipient_id = user_id;

it's the same as you asked but I've laid it out this way as I use workbench. I've used OR in the statement.

As pointed out in the comments, in SQLlite || is specifically a concatenation operator and does not operate the same as OR. Apologies for my previous error!