MySQL - get previous and next rows in one query

Over on the Cortex blog we have a janky-ass blog system that uses some filthy PHP and MySQL to do news article type things. I wrote it, it’s disgusting, we should do better, but it is what it is.

The plumber’s toilet, and all that.

Pending some time to actually do it properly, we wanted to polish the turd enough that it was semi-functional - and this included having in-post navigation - so that, from a post you could either jump forward or backwards to the post. Much like on this blog. Which Hugo somehow just does automagically.

I won’t go in to the underlying structure extensively, but you cannot rely on e.g., the next and previous IDs of each post being the correct thing to navigate to. In fact, you need to navigate to the correct post in chronological (based on post-date) order (NOT ID order) but also check for things like is it still published (not been deleted) and assorted other things. This would be a trivial job to do IF - and that’s the kicker - IF you were happy issuing database calls for the post content, the previous link and the next link… i.e. at least 3 separate DB calls to get this stuff. That seems over the top.

This is by no means a new or modern issue, and StackOverflow is awash with suggestions, but again, none of them quite cut the mustard. There was even this effort which got most of the way there. But this still required too many queries (I don’t entirely buy that they are doing it in only 2… given that each piece has a sub-select.)

Using new-found knowledge about MySQL’s query variables - as part of the HashOver comment migration fun - I eventually arrived at something that moreorless does it and moreorless does it with a single call - ish.

I should mention this is MySQL 5… if this was MySQL 8 then it would be a lot simpler with window functions!

Query for preceding and following rows

OK so given a key - i.e., the item that is in the middle of the set of things we are looking for, in this case @slug, we set up two variables to (a) find the matching row and then (b) track the other rows relative to it.

set @rowpos = 0;
set @slugpos = 0;
set @slug = 'find-this';

SELECT slug,  
@rowpos := @rowpos + 1 rowpos, 
@slugpos := CASE
            WHEN @slug = slug 
            THEN @slugpos := @rowpos ELSE @slugpos END slugpos
FROM `the_table` 
WHERE `stuff` 
ORDER BY `stuff``

Run this in your place and you will get the ‘slug’ and their position AND if it was the matching one, that will be snapshotted,

You can then expand this out to grab the previous and following:

set @rowpos = 0;
set @slugpos = 0;
set @slug = 'find-this';

SELECT *
FROM 
(
SELECT slug, title, publish_date, 
@rowpos := @rowpos + 1 rowpos, 
@slugpos := CASE
            WHEN @slug = slug 
            THEN @slugpos := @rowpos ELSE @slugpos := @slugpos END slugpos
FROM `the_table` 
WHERE `stuff` 
ORDER BY `stuff``
    ) slugs
WHERE rowpos = @slugpos - 1 or rowpos = @slugpos + 1

So there you have it - in one, ish, call - you will get the preceding and following rows in a MySQL database call.

Using this in PDO

IF, and this is pretty likely, you’re running this in a PHP PDO query (and you should be for many MANY reasons… just do it, OK) then trying to fire that lot in to a single statement will fail with a General Failure.

PDO can only do one thing at once. Rubbish.

You will therefore need to split it…

$query = "
	set @rowpos = 0;
	set @slugpos = 0;
	set @slug = ?;
";
$stmt = $this->pdo->prepare($query);
$stmt->execute([$slug]);

followed by

$query = "SELECT slug, title, rowpos, @slugpos slugpos
	FROM 
	(
		SELECT slug, title, publish_date, 
		@rowpos := @rowpos + 1 rowpos, 
		@slugpos := CASE
			WHEN @slug = slug 
						THEN @slugpos := @rowpos ELSE @slugpos END slugpos
			FROM `the_table` 
  	WHERE `stuff``
		ORDER BY `stuff`
		) slugs
	  WHERE rowpos = @slugpos - 1 or rowpos = @slugpos + 1";

$stmt = $this->pdo->prepare($query);
$stmt->execute();			
$data = $stmt->fetchAll();	

This should get you going.