CODEWITHSUNDEEP
sqlite
Vns Aditya
Vns Aditya

Reputation: 445

How can I execute multiple queries in one query?

I am building a chat application where I am using Firebase to send and receive messages. Once I send or receive a message, I am storing it to SQLite as follows. Now it the recent chats screen, I need the last message from all the unique chats, number of unread messages in those unique chats in one single query as I am observing the SQLite database.

Mid(STRING)     | SentBy | SentTo | message | readTime | sentTime| Type
----------------+--------+--------+---------+----------+---------+------
A               | AA     | JD     | M1      |   1      |    0    |  S
B               | JD     | AA     | M2      |   2      |    1    |  s
C               | AA     | JD     | M3      |   3      |    2    |  s
D               | AB     | JD     | m5      |   null   |    3    |  s
E               | AA     | JC     | M1      |   5      |    4    |  s
F               | JD     | AB     | M2      |   6      |    5    |  s
G               | AA     | JD     | M3      |   7      |    6    |  s
H               | AA     | JC     | m5      |   8      |    7    |  s
I               | AA     | JD     | M1      |   null   |    8    |  s
J               | JD     | AA     | M2      |  10      |    9    |  s
K               | AA     | JD     | M3      |  11      |    10   |  s
L               | AB     | JC     | m5      |  12      |    11   |  s
M               | AA     | JD     | M1      |  13      |    12   |  s
N               | JC     | AA     | M2      |  14      |    13   |  s
O               | AB     | JD     | M3      |  15      |    14   |  s
P               | JC     | JD     | m5      |  16      |    15   |  s

I tried

SELECT *,COUNT() FROM messagesTable GROUP BY min ( sentBy, sentTo ), max( sentBy , sentTo ) ORDER BY sentTime desc

This query gives me the last messages from every combination of sentTo and sentBy. But I also need to know how many messages are unread for that combination. I want to run a query for every row like

SELECT COUNT() FROM messagesTable WHERE sentBy = message.sentBy, sentTo = message.sentTo, readTime = null

How can I run both queries in a single query?

Upvotes: 1

Views: 445

Answers (1)

forpas
forpas

Reputation: 164069

You must group by the combination of (sentby, sentto) and with a straight count(*) get the total number of messages and with conditional aggregation you can get the number of unread mesages.
Then join to the result to the table to get also the last message:

select 
  g.user1, g.user2, g.lasttime, m.message lastmessage, 
  g.totalcounter, g.unreadcounter
from messagestable m inner join (
  select 
    min(sentby, sentto) user1, max(sentby, sentto) user2,
    max(senttime) lasttime, count(*) totalcounter,
    sum(case when readtime is null then 1 else 0 end) unreadcounter
  from messagestable
  group by user1, user2
) g 
on g.user1 = min(m.sentby, m.sentto) and g.user2 = max(m.sentby, m.sentto) 
and g.lasttime = m.senttime
order by g.lasttime desc

See the demo.
Results:

| user1 | user2 | lasttime | lastmessage | totalcounter | unreadcounter |
| ----- | ----- | -------- | ----------- | ------------ | ------------- |
| JC    | JD    | 15       | m5          | 1            | 0             |
| AB    | JD    | 14       | M3          | 3            | 1             |
| AA    | JC    | 13       | M2          | 3            | 0             |
| AA    | JD    | 12       | M1          | 8            | 1             |
| AB    | JC    | 11       | m5          | 1            | 0             |

Upvotes: 2

Related Questions