It's been a while since I was working with SQL queries. Today I encounter a problem with fetching data from a table joined with referencing records from another table. I need support with constructing a valid SQL query, that will work with the SQLite database.
The problem
Given the following tables in SQLite database:
CREATE TABLE IF NOT EXISTS "contacts" (
"id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
"name" text NOT NULL
);
CREATE TABLE IF NOT EXISTS "messages" (
"id" integer PRIMARY KEY AUTOINCREMENT NOT NULL,
"date" integer NOT NULL,
"senderId" integer NOT NULL,
"recipientId" integer NOT NULL,
"text" text NOT NULL
);
where:
messages.senderIdreferencescontacts.idmessages.recipientIdreferencescontacts.id
For contact with provided id, I would like to fetch all other contacts with the newest message that was either sent to or received from that contacts. The results should be ordered descending by the message date.
An example
For example, if we have the following contacts:
---- ------
| id | name |
---- ------
| 1 | A |
---- ------
| 2 | B |
---- ------
| 3 | C |
---- ------
and the following messages:
---- ------ ---------- ------------- ----------
| id | date | senderId | recipientId | text |
---- ------ ---------- ------------- ----------
| 1 | 10 | 1 | 2 | A→B @ 10 |
---- ------ ---------- ------------- ----------
| 2 | 20 | 2 | 1 | B→A @ 20 |
---- ------ ---------- ------------- ----------
| 3 | 30 | 1 | 2 | A→B @ 30 |
---- ------ ---------- ------------- ----------
| 4 | 40 | 1 | 3 | A→C @ 40 |
---- ------ ---------- ------------- ----------
| 5 | 50 | 3 | 1 | C→A @ 50 |
---- ------ ---------- ------------- ----------
| 6 | 60 | 2 | 3 | B→C @ 60 |
---- ------ ---------- ------------- ----------
| 7 | 70 | 3 | 2 | C→B @ 70 |
---- ------ ---------- ------------- ----------
The fetch result for the contact "A" should look like this:
--------------- --------------- ---------------
| contacts.name | messages.date | messages.text |
--------------- --------------- ---------------
| C | 50 | C→A @ 50 |
--------------- --------------- ---------------
| B | 30 | A→B @ 30 |
--------------- --------------- ---------------
The same fetch, but for the contact "B", should result in the following output:
--------------- --------------- ---------------
| contacts.name | messages.date | messages.text |
--------------- --------------- ---------------
| C | 70 | C→B @ 70 |
--------------- --------------- ---------------
| A | 30 | A→B @ 30 |
--------------- --------------- ---------------
And for the contact "C", we should get:
--------------- --------------- ---------------
| contacts.name | messages.date | messages.text |
--------------- --------------- ---------------
| B | 70 | C→B @ 70 |
--------------- --------------- ---------------
| A | 50 | C→A @ 50 |
--------------- --------------- ---------------
My failed attempt
Honestly, I feel lost and I'm not sure if what I'm doing is even close to the correct solution, but here is the SQL query I've tried to use:
WITH "lastMessage" AS (
SELECT *, MAX("date")
FROM "messages"
GROUP BY "senderId" OR "recipientId"
)
SELECT "contacts"."name", "lastMessage"."date", "lastMessage"."text"
FROM "contacts"
JOIN "lastMessage"
ON ("lastMessage"."senderId" = "contacts"."id")
OR ("lastMessage"."recipientId" = "contacts"."id")
WHERE "contacts"."id" != 1
ORDER BY "lastMessage"."date" DESC
Unfortunately, it doesn't work.
Any feedback will be much appreciated!
CodePudding user response：
Join messages to 2 copies of contacts and aggregate:
SELECT c2.name, MAX(m.date) last_date, m.text
FROM messages m
INNER JOIN contacts c1 ON c1.id IN (m.senderId, m.recipientId)
INNER JOIN contacts c2 ON c2.id IN (m.senderId, m.recipientId) AND c1.id <> c2.id
WHERE c1.name = ?
GROUP BY c2.id
ORDER BY last_date DESC;
Change ? to the name of the contact that you want.
See the demo.