Getting row before and after a query

Question

I currently have this query

SELECT  short_url,
        type,
        file_thumbnail,
        file_embed,
        media.id,
        user,
        media.file_url,
        votes,
        comments,
        GROUP_CONCAT(info.itemTime) AS time,
        info.title,
        media.time AS oldTime,
        media.title,
        info.topic,
        GROUP_CONCAT(votes) AS totalVotes,
        GROUP_CONCAT(votes) AS listVotes, 
        GROUP_CONCAT(comments) AS listComments, 
        GROUP_CONCAT(url) AS listSites 
from    info JOIN 
        media on info.mid=media.id 
WHERE   media.id='$id' 
GROUP BY    mid 
ORDER BY    media.id DESC 
LIMIT 0,1

I'm adding a new feature that lets you easily navigate to the previous or next item. Is there an easy way to modify the above query so that it pulls the prior row, current row and the next row? Or would it be easier to just do another query?

Answer 1

You could try pulling multiple rows (i.e., a page of results) with the initial query, then navigating through that data on the front end. If you need to have up-to-the-instant accurate data from the database, I don't see any other way than just doing an additional query.

Answer 2

You could use a union to get the previous and next rows:

SELECT
    ...
FROM info
INNER JOIN media
    ON info.mid = media.id
WHERE media.id < '$id'
GROUP BY mid
ORDER BY media.id DESC
UNION ALL
SELECT
    ...
FROM info
INNER JOIN media
    ON info.mid = media.id
WHERE media.id = '$id'
GROUP BY mid
ORDER BY media.id DESC
UNION ALL
SELECT
    ...
FROM info
INNER JOIN media
    ON info.mid = media.id
WHERE media.id > '$id'
GROUP BY mid
ORDER BY media.id ASC

However, if your user will want to see the previous item or next item each time they navigate, then you're still going to hit the database to get the next item in whatever direction they're navigating.

I would recommend pulling a page at a time, and navigating through it on the front end instead.

Answer 3

You can use subqueries to get the id of the previos and next record:

...
where media.id in (
  '$id',
  (select max(id) from media where id < '$id'),
  (select min(id) from media where id > '$id')
)
...

Answer 4

Here's a couple key points your app should try to maintain:

• Don't load more data than necessary. (You don't know if the user will use the prev/next rows or not.)
• Make URLs sane, using stable URLs wherever possible. (The URL should not indicate that you want to be 3 next from row #127.)

Keeping those goals in mind, I'd recommend that on clicking the "next row" link, the page pulls the next row's ID, then redirects to the permalink for that row. So, for example:

http://example.com/media/1337/prev should run:

SELECT id FROM media WHERE media.id < 1337 ORDER BY media.id DESC LIMIT 1

And redirect to, presumably, http://example.com/media/1336.

http://example.com/media/1337/next should run:

SELECT id FROM media WHERE media.id > 1337 ORDER BY media.id ASC LIMIT 1

And redirect to, presumably, http://example.com/media/1338.

Answer 5

Do you really want the next row in the database? Is you data indexed on info.id? If not your next row might not be the item you expect. What do you do if you need to change the order of items?

You could consider having next_id and previous_id as fields on info, and then

SELECT
    ...
FROM info current_info
INNER JOIN media current_media
    ON current_info.id = current_media.id

INNER JOIN info prev_info
    ON prev_info.id = current_info.previous_id 
INNER JOIN media prev_media
    ON prev_info.id = prev_media.id

INNER JOIN info next_info
    ON next_info.id = current_info.next_id 
INNER JOIN media next_media
    ON next_info.id = next_media.id

WHERE current_media.id = '$id'

This has the advantages of always returning a single row. Why is that good? If there is no previous or no next how do you tell if your two returned rows are previous and current, or current and next.

It is also more manageable as you can reorder items by changing their sibling ids.

(WARNING: the code above may not work, i have no sql client on my laptop. But hopefully it will point you in a good direction)