CODEWITHSUNDEEP
androidsqlite
jinkal
jinkal

Reputation: 1702

How do I combine these two SQLite queries?

How do I combine these two queries into a single query to get the last 10 messages, using the LIMIT keyword?

Cursor cursor0 = db.query(Chat_MESSAGE, messagecolumns, senderid + "=? AND " + receiverid + "=?", new String[] {
            sender_id, receiver_id }, null, null, null, null);

Cursor cursor1 = db.query(Chat_MESSAGE, messagecolumns, senderid + "=? AND " + receiverid + "=?", new String[] {
            receiver_id, sender_id }, null, null, null, null);

Table is

chat id sender_id receiver_id message
   1        1       2            hii
   2        1       2            hello
   3        2       1            good
   4        2       1            nice
   5        1       5            hii

I want to get 1-4 records.

Upvotes: 0

Views: 43

Answers (2)

CL.
CL.

Reputation: 180310

These are your original two conditions:

senderid = ? AND receiverid = ?

receiverid = ? AND senderid = ?

To combine them, use OR:

(senderid = ? AND receiverid = ?) OR
(receiverid = ? AND senderid = ?)

This requires four parameter values. With numbered parameters, you can reuse them instead:

(senderid = ?1 AND receiverid = ?2) OR
(receiverid = ?1 AND senderid = ?2)

Upvotes: 2

Nick Cardoso
Nick Cardoso

Reputation: 21783

The trick to doing a LIMIT (or a GROUP_BY) is to tack it onto the end of the SORT clause. For example, presuming you have your id column named senderId you could use the SORT clause "senderid DESC LIMIT 10"

Now I'm not sure what db is (probably the database directly) but you should be using a content provider so the params are 'uri, projection, selection, arguments, sort' and the following should be what you want

cursor = cp.query(Chat_MESSAGE, messagecolumns, "senderid=? AND " + "receiverid=?", new String[] { sender_id, receiver_id }, "senderid DESC LIMIT 10");

If this question arose because you don't know how to read the cursor jsut add a comment and I'll show you

Upvotes: 0

Related Questions