Left joining Mysql with link table and ORDER and LIMIT

Question

I am developing a chat feature that has the tables cases, entries and attachments. Each chat (case) has chat entries that has one or more attachments. The attachments can be for example a text message or a video message. Now I am struggling to present the last chat message for a particular chat (case) in the chat overview - I only manage to get the last chat message of one of the chat conversations (the latest one).

The expected outcome should be a list of ongoing chat conversations with title and the last chat message, like:

IT-support chat

I have solved the issue with the...

Customer service chat

It's easy, you just have to resta...

Simplified table structure:

Cases

 id  |      title
-----|-----------------
  1  |  IT-support chat

Entries

 id  |    case_id   | time_created
-----|--------------|--------------
 15  |       1      | ...
 16  |       1      | ...

Attachments

 id  |   entry_id   |              payload
-----|--------------|--------------------------------------------
  1  |      15      | Who solved the issue?
  2  |      16      | I have solved the issue with the meatballs

This query picks just the last chat message for one of the cases and not the last chat message per case:

SELECT 
    cases.title AS caseTitle,
    last_chatmsg.payload AS lastChatMsg
FROM
    cases 
LEFT JOIN (
    SELECT
        entries.case_id,
        attachments.payload
    FROM
        entries, attachments
    WHERE
        entries.id = attachments.entry_id
    ORDER BY
        entries.time_created DESC
    LIMIT 1
    )
    last_chatmsg ON last_chatmsg.case_id = cases.id
GROUP BY cases.id

Any ideas of how to get this to work?

*The expected outcome should be a list of ongoing chat conversations with title and **the last** chat message* `SELECT * FROM cases JOIN entries JOIN attachments ORDER BY created_at DESC LIMIT 1`. — Akina, Mar 05 '20 at 08:24

score 0 · Answer 1 · answered Mar 05 '20 at 10:07

Most recent chat per case:

SELECT x.case_id, x.id FROM 
  (SELECT case_id, id, ROW_NUMBER(OVER PARTITION BY case_id ORDER BY time_created DESC) rn FROM entries)
WHERE x.rn = 1

Turn it into a CTE:

WITH mrc AS

    SELECT x.case_id, x.id FROM 
      (SELECT case_id, id, ROW_NUMBER(OVER PARTITION BY case_id ORDER BY time_created DESC) rn FROM entries)
    WHERE x.rn = 1

SELECT * FROM
  cases c
  INNER JOIN mrc m ON c.id = m.case_id
  LEFT JOIN attachments a ON mrc.id = a.entry_id

Left joining Mysql with link table and ORDER and LIMIT

1 Answers1