CODEWITHSUNDEEP
mysqllaravel
mukeshsoni
mukeshsoni

Reputation: 583

Get 2 Previous and Next with Current entry from database in laravel

I'm showing the image on a page. I am getting data with this

 $designs = Design::take(10)->where('show', '1')->where('id', $id)->orderBy('hit', 'desc')->get();

Now I want 2 previous and 2 next records so users can select and see them. Something like

Select 5 entry where the middle should be 'id'

Upvotes: 0

Views: 48

Answers (1)

IGP
IGP

Reputation: 15851

The solution I came up with is the following:

  1. You find the middle design.
  2. Using it's id, you can get the previous 2 and make an union with a similar query that gets the next 2 (along with the original).
$design = Design::select('id')
    ->where('name', 'Sunset')
    ->firstOrFail();

$designs = Design::query()
    ->where('id', '<', $design->id)
    ->orderByDesc('id')
    ->limit(2)
    ->union(
        Design::query()
            ->where('id', '>=', $design->id)
            ->orderBy('id')
            ->limit(3)
    )
    ->orderBy('id')
    ->get();

The SQLs generated by this query are the following:

SELECT `id` FROM `designs`
WHERE `name` = "Sunset"
LIMIT 1

(
    SELECT * FROM `designs`
    WHERE `id` < ?
    ORDER BY `id` DESC
    LIMIT 2
)
UNION
(
    SELECT * FROM `designs`
    WHERE `id` >= ?
    ORDER BY `id` ASC
    LIMIT 3
)
ORDER BY `id` ASC

... Or if you'd rather have the results more separated

$design   = Design::where('name', 'Sunset-Image')->firstOrFail();
$previous = Design::where('id', '<', $design->id)->orderByDesc('id')->limit(2)->get();
$next     = Design::where('id', '>', $design->id)->orderBy('id')->limit(2)->get();

Upvotes: 1

Related Questions