SELECT M.Id
FROM messageRecipients MR
INNER JOIN messages M ON M.Id = MR.messageId
INNER JOIN users S ON S.Id = M.senderId
WHERE MR.userId = 149
AND MR.deleted = 0
AND MR.readAt IS NULL
ORDER BY M.sentAt ASC
I need to do a previous and next with this query. So I have a function that is passed in a message id (M.id). I need to select the previous and next message in the query. How would I do that?
yes. basically I need to take that query, and given a message id, I need to get the previous and next message id (by sentAt because that's how it is ordered)
You can either calculate the time distance, as I've suggested in a previous post - viewtopic.php?f=2&t=94490
Or perform a union on the same query twice, ordered in different directions (for example, for message id = 130):
(
SELECT M.Id
FROM messageRecipients MR
INNER JOIN messages M ON M.Id = MR.messageId
INNER JOIN users S ON S.Id = M.senderId
WHERE MR.userId = 149
AND MR.deleted = 0
AND MR.readAt IS NULL
AND M.sentAt > (SELECT sentAt FROM messages WHERE messages.Id = 130)
ORDER BY M.sentAt ASC
LIMIT 1
) UNION ALL (
SELECT M.Id
FROM messageRecipients MR
INNER JOIN messages M ON M.Id = MR.messageId
INNER JOIN users S ON S.Id = M.senderId
WHERE MR.userId = 149
AND MR.deleted = 0
AND MR.readAt IS NULL
AND M.sentAt < (SELECT sentAt FROM messages WHERE messages.Id = 130)
ORDER BY M.sentAt DESC
LIMIT 1
)
The Ninja Space Goat wrote:yes. basically I need to take that query, and given a message id, I need to get the previous and next message id (by sentAt because that's how it is ordered)
[sql] SET @curr_sentAt=(SELECT sentAt FROM message WHERE id=$id);SELECT (SELECT max(id) FROM message WHERE sentAt < @curr_sentAt) AS prev, (SELECT min(id) FROM message WHERE sentAt > @curr_sentAt) AS next[/sql]
Last edited by VladSun on Mon Feb 02, 2009 3:02 pm, edited 1 time in total.
There are 10 types of people in this world, those who understand binary and those who don't
It needs to be the id of next message sorted by sentAt, not by the id itself. There are all the additional constraints on the message in addition with the user id etc
Yeah, I've just notice the sentAt thing.
Others constraints are easy to implement and most of them do not apply to "and given a message id, I need to get the previous and next message id (by sentAt because that's how it is ordered)", so they should be considered redundant.
There are 10 types of people in this world, those who understand binary and those who don't
Though it's not so nice as I though it would be (before I noticed the sentAt condition) I' posting it :
[sql]SET @curr_sentAt=(SELECT sentAt FROM message WHERE id=$id);SELECT ( SELECT id FROM message WHERE sentAt = ( SELECT max(sentAt) FROM message WHERE sentAt < @curr_sentAt) ) ) AS prev, ( SELECT id FROM message WHERE sentAt = ( SELECT min(sentAt) FROM message WHERE sentAt > @curr_sentAt) ) ) AS next [/sql]
There are 10 types of people in this world, those who understand binary and those who don't