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 the all 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)
        ->unionAll($prev)
        ->unionAll($next)
        ->get();

// now $res is an array of 3 objects
// main, prev, next
dd($res);

** 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)
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s