Page 1 of 1

query help - get next?

Posted: Mon Feb 02, 2009 1:15 pm
by Luke
I didn't sleep at all last night so maybe I'm just brain-dead, but I can't think of how to do this. I have a query like:

Code: Select 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
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?

Re: query help - get next?

Posted: Mon Feb 02, 2009 1:34 pm
by Eran
Next and previous according to what criterion? sentAt?

Re: query help - get next?

Posted: Mon Feb 02, 2009 1:36 pm
by Luke
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)

Re: query help - get next?

Posted: Mon Feb 02, 2009 1:51 pm
by Eran
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):

Code: Select 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 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
)

Re: query help - get next?

Posted: Mon Feb 02, 2009 2:56 pm
by VladSun
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]

Re: query help - get next?

Posted: Mon Feb 02, 2009 3:00 pm
by Eran
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

Re: query help - get next?

Posted: Mon Feb 02, 2009 3:04 pm
by VladSun
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.

Re: query help - get next?

Posted: Mon Feb 02, 2009 3:06 pm
by Eran
Using a parameter is a nice addition for cleaning up the query a little :)
Why did you leave the max/min though?

Re: query help - get next?

Posted: Mon Feb 02, 2009 3:10 pm
by VladSun
pytrin wrote:Using a parameter is a nice addition for cleaning up the query a little :)
Why did you leave the max/min though?
Ouch :) My bad :)

Re: query help - get next?

Posted: Mon Feb 02, 2009 3:27 pm
by VladSun
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]