query help - get next?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

query help - get next?

Post 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?
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: query help - get next?

Post by Eran »

Next and previous according to what criterion? sentAt?
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Re: query help - get next?

Post 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)
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: query help - get next?

Post 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
)
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: query help - get next?

Post 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]
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
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: query help - get next?

Post 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
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: query help - get next?

Post 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.
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
Eran
DevNet Master
Posts: 3549
Joined: Fri Jan 18, 2008 12:36 am
Location: Israel, ME

Re: query help - get next?

Post by Eran »

Using a parameter is a nice addition for cleaning up the query a little :)
Why did you leave the max/min though?
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: query help - get next?

Post 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 :)
There are 10 types of people in this world, those who understand binary and those who don't
User avatar
VladSun
DevNet Master
Posts: 4313
Joined: Wed Jun 27, 2007 9:44 am
Location: Sofia, Bulgaria

Re: query help - get next?

Post 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]
There are 10 types of people in this world, those who understand binary and those who don't
Post Reply