Writing a SQL request with a CTE and counting its rows-CodePudding

I have two tables: Contacts and Messages. I'd like to fetch a Chat structure that doesn't belong to any table (in other words: there's no Chats table I just want to build a query) This query should contain:

A contact I'm referring to in that chat
A lastMessage between me and that contact (If I don't have a last message w/ that contact - I should get no result from that contact specifically)
unreadCount that tells how many messages inside that conversation are not read yet.

My tables are:

Contacts

uniqueId (Blob)
username (Text)

Messages

isRead (Bool)
sender (Blob)
receiver (Blob)
timestamp (Integer)

The farthest I got was this:

WITH 
"lastMessage" AS (
    SELECT *, MAX("timestamp") 
    FROM "messages" 
    GROUP BY "sender", "receiver"
),
"unreadCount" AS (
    SELECT COUNT(*)
    FROM "messages" WHERE "isRead" = 0
    GROUP BY "sender"
)

SELECT "contacts".*, "unreadCount".*, "lastMessage".* 
FROM "contacts" 

JOIN "lastMessage" 
ON ("lastMessage"."sender" = "contacts"."uniqueId") 
OR ("lastMessage"."receiver" = "contacts"."uniqueId") 

LEFT JOIN "unreadCount" 
ON "unreadCount"."sender" = "contacts"."uniqueId" 

ORDER BY "lastMessage"."timestamp" DESC

PS: The sender/receiver of a message is equal to a contact uniqueId (or my own uniqueId and vice-versa)

The issue with the current result I'm having is that its fetching duplicate chats for each contact (considering each one of them has a sent and a received message) and its not working to fetch the un-reads

Edit 1: Here's what I'm looking for...

I have on my DB:

Contacts table:

- username: John Doe, 
  uniqueId: (Blob...)

- username: Alice Shrek, 
  uniqueId: (Blob...)

Messages table:

- sender: (Blob from my uniqueId), 
  receiver: (Blob from John Doe), 
  isRead: true, 
  timestamp: ...1

- sender: (Blob from John Doe), 
  receiver: (Blob from my uniqueId), 
  isRead: false, 
  timestamp: ...2

- sender: (Blob from my uniqueId), 
  receiver: (Blob from Alice Shrek), 
  isRead: true, 
  timestamp: ...3

- sender: (Blob from Alice Shrek), 
  receiver: (Blob from my uniqueId), 
  isRead: false, 
  timestamp: ...4

(In other words, this hypothetical scenario I have two contacts and two messages w/ each of those contacts. One message I sent which is the read one and one I received which is the unread one)

I want my query to fetch these:

- username: John Doe, 
  uniqueId: (Blob from John doe), 
  sender: (Blob from John doe)
  receiver: (Blob from my uniqueId)
  timestamp: ...2
  unreadCount: 1

- username: Alice Shrek, 
  uniqueId: (Blob from Alice Shrek), 
  sender: (Blob from Alice Shrek)
  receiver: (Blob from my uniqueId)
  timestamp: ...4
  unreadCount: 1

CodePudding user response：

I think this has all you want in it ... EDIT: First pass missed Unread count!

DECLARE @Me int = 104;

with cteConatacts as (
    SELECT * FROM (VALUES (101, 'Bob'), (102, 'Alice')
        , (103, 'Chris'), (104, 'Me')) as Contacts(ContactID, ContactName)
), cteMessages as (
    SELECT * FROM (VALUES (1, 101, 103, 1001, 'Hi Chris')
        , (1, 101, 104, 1002, 'Hi friend')
        , (1, 104, 101, 1003, 'Hi Bob')
        , (0, 104, 102, 1004, 'Hi Alice')
    ) as Mess(IsRead, SenderID, RecvID, Timestmp, MsgBody)
), cteThreadsWithMe as (
    SELECT 'Sent' as Direction, @Me as MyID, M.RecvID as TheirID,  M.* 
    FROM cteMessages as M 
    WHERE M.SenderID = @Me 
    UNION ALL
    SELECT 'Recv' as Direction, @Me as MyID, M.SenderID as TheirID,  M.* 
    FROM cteMessages as M 
    WHERE M.RecvID = @Me 
), cteGrouped as (
    SELECT * 
        , ROW_NUMBER() OVER (PARTITION BY MyID, TheirID ORDER BY Timestmp DESC) as Newness
    FROM cteThreadsWithMe
), cteReadCounts as (
    SELECT G.MyID, G.TheirID, Count(*) as MessageCount, SUM( IsRead) as ReadCount
    FROM cteGrouped as G
    GROUP BY G.MyID, G.TheirID 
)
SELECT CM.ContactName as MyName, CT.ContactName as TheirName,G.* , C.MessageCount - C.ReadCount as UnreadCount
FROM cteGrouped as G 
    INNER JOIN cteConatacts as CM on CM.ContactID = G.MyID 
    INNER JOIN cteConatacts as CT on CT.ContactID = G.TheirID 
    INNER JOIN cteReadCounts as C on C.MyID = G.MyID AND C.TheirID = G.TheirID 
WHERE Newness = 1

Output:

MyName	TheirName	Direction	MyID	TheirID	IsRead	SenderID	RecvID	Timestmp	MsgBody	Newness	UnreadCount
Me	Bob	Sent	104	101	1	104	101	1003	Hi Bob	1	0
Me	Alice	Sent	104	102	0	104	102	1004	Hi Alice	1	1

CodePudding user response：

Use window function ROW_NUMBER() to identify the last message and TOTAL() to get the total number of unread messages of each contact:

WITH
  person(uniqueId) AS (VALUES (?)),
  aggregates AS (
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY MIN(m.sender, m.receiver), MAX(m.sender, m.receiver) ORDER BY timestamp DESC) rn,
           TOTAL(NOT m.isRead) OVER (PARTITION BY MIN(m.sender, m.receiver), MAX(m.sender, m.receiver)) unreadCount
    FROM Messages m INNER JOIN person p 
    ON p.uniqueId IN (m.sender, m.receiver)
  )
SELECT c.username, c.uniqueId,
       a.sender, a.receiver, a.timestamp, a.unreadCount 
FROM aggregates a CROSS JOIN person p 
INNER JOIN Contacts c 
ON c.uniqueId = CASE WHEN a.sender = p.uniqueId THEN a.receiver ELSE a.sender END
WHERE a.rn = 1;

Replace ? in the cte person with your uniqueId.

See the demo.