Get Prev & Next Records in Laravel (v4+, v5+)

sometimes you want to get the prev/next records of the current id but not just the ids,
maybe to have something like previous article / next article ‘like the one at the bottom of this post‘ but without making too many queries to the db.

so the easiest way i found was combining all of the 3 queries into one “find an article by id, and fetch the closest prev & next ones to that id

// for example
$id   = 7;

$prev = DB::table('articles')->where('id', '<', $id)->orderBy('id','desc')->limit(1);
$next = DB::table('articles')->where('id', '>', $id)->limit(1);

$res = DB::table('articles')
        ->where('id', '=', $id)

// now $res is an array of 3 objects
// main, prev, next

** and the sql query is only

(select * from `articles` where `id` = ?)
union all (select * from `articles` where `id` < ? order by `id` desc limit 1)
union all (select * from `articles` where `id` > ? limit 1)

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.