Here is the query I am using to retrieve the latest message from each room:
SELECT MAX
( "Messages"."id" ) AS messageId,
"Rooms"."id"
FROM
"RoomUsers"
INNER JOIN "Rooms" ON "RoomUsers"."roomId" = "Rooms"."id"
INNER JOIN "Conversations" ON "Conversations"."roomId" = "Rooms"."id"
INNER JOIN "Messages" ON "Messages"."conversationId" = "Conversations"."id"
WHERE
"RoomUsers"."userId" = 51
GROUP BY
"Rooms"."id"
The expected output would look something like this:
+----------+-------+
|messageId | id |
+----------+-------+
| 1 | 4 |
| 17 | 5 |
| 7 | 6 |
| 29 | 7 |
| 18 | 8 |
| 19 | 9 |
+----------+-------+
As you can see, handling large datasets efficiently is crucial. To tackle this issue, I plan to implement pagination using OFFSET and LIMIT.
To start off, I set up a simple pagination with 3 records per page:
SELECT MAX
( "Messages"."id" ) AS messageId,
"Rooms"."id"
FROM
"RoomUsers"
INNER JOIN "Rooms" ON "RoomUsers"."roomId" = "Rooms"."id"
INNER JOIN "Conversations" ON "Conversations"."roomId" = "Rooms"."id"
INNER JOIN "Messages" ON "Messages"."conversationId" = "Conversations"."id"
WHERE
"RoomUsers"."userId" = 51
GROUP BY
"Rooms"."id"
LIMIT 3 OFFSET 0
Upon executing this query, the result for the first page is as follows:
+----------+-------+
|messageId | id |
+----------+-------+
| 1 | 4 |
| 17 | 5 |
| 7 | 6 |
+----------+-------+
This seems to work fine for one page. However, when changing the offset to 1 for the second page, the result changes:
+----------+-------+
|messageId | id |
+----------+-------+
| 17 | 5 |
| 7 | 6 |
| 29 | 7 |
+----------+-------+
Increasing the offset further to 2 for the third page produces a different outcome:
+----------+-------+
|messageId | id |
+----------+-------+
| 7 | 6 |
| 29 | 7 |
| 18 | 8 |
+----------+-------+
There seems to be some confusion in the results as we paginate through them. I need to understand why this is happening and how I can rectify it for proper data pagination.