Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

visualight's avatar

SortBy price with limit and offset on multiple tables

Hello everybody,

I tear my hair for a few days to try to find a solution to my problem. I have 3 tables through which I want to sort the results by price (desc or asc) to display on a page.

The problem I think is at near the "limit and offset." I think in fact that "Eloquent" does not understand that he must get the elements of my table with priority the ASC or DESC price. Indeed, at page 0 (limit 20, offset 0), all prices are properly sorted, but when I load another page, I found some price to $ 1, while the last record of the first page (limit 20, offset 0 is $ 3).

How to keep the system "limit + offset" while having the results sorted properly?

Here is part of my code ... Thank you

$limit = 20;

if($page==0)
        {
            $start = 0;
        }
        else
        {
            $start = $page*$limit;
        }
if($type == '')
            {
                $albumsOrder1 = [
                    'type' => 'album_price',
                    'value' => 'asc'
                ];

                $objectsOrder1 = [
                    'type' => 'object_price',
                    'value' => 'asc'
                ];

                $contactsOrder1 = [
                    'type' => 'contact_price',
                    'value' => 'asc'
                ];
            }
$albums1 = AlbumUser::with(array('pictures' => function($q){
                $q->where('status', 2);
                $q->orderBy('order', 'asc');
            }))->with(array('users' => function($u){
                $u->with('commission');
            }))->where(function($r){
                $r->where('status', 2);
                $r->where('active', 1);
                $r->where('album_promo', 0);
            })->orderBy($albumsOrder1['type'], $albumsOrder1['value'])->orderBy('created_at', 'desc')->limit($limit)->offset($start)->get(array('*', 'album_price as price'));

            $objects1 = ObjectUser::with(array('users' => function($u){
                $u->with('commission');
            }))->where(function($q){
                $q->where('active', 1);
                $q->where('status', 2);
                $q->where('object_promo', 0);
            })->where(function($r){
                $r->where('object_quantity', -1);
                $r->orWhere('object_quantity', '>', 0);
            })->with('canvas')->orderBy($objectsOrder1['type'], $objectsOrder1['value'])->orderBy('created_at', 'desc')->limit($limit)->offset($start)->get(array('*', 'object_price as price'));

            $contacts1 = ContactUser::with(array('users' => function($u){
                $u->with('commission');
            }))->where(function($q){
                $q->where('status', 2);
                $q->where('active', 1);
                $q->where('contact_promo', 0);
            })->where(function($r){
                $r->where('contact_quantity', -1);
                $r->orWhere('contact_quantity', '>', 0);
            })->with('canvas')->orderBy($contactsOrder1['type'], $contactsOrder1['value'])->orderBy('created_at', 'desc')->limit($limit)->offset($start)->get(array('*', 'contact_price as price'));

$collection1 = $albums1->merge($objects1);
$data = $collection1->merge($contacts1);

if($filter == 'priceDesc')
        {
            $data = $data->sortByDesc('price');
            $data = $data->values()->all();
        }
        elseif($filter == 'priceAsc')
        {
            $data = $data->sortBy('price');
            $data = $data->values()->all();
        }
      dd($data);
0 likes
1 reply
kyslik's avatar

Wow, to read and understand code above I would need 2 or 3 coffees, could you elaborate what do you want to achieve?

Include your model(s) (code).

Why don't you use pagination out of Laravel box?

Please or to participate in this conversation.