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

movepixels's avatar

Paginate a query

Looking for some help with a paginated query sort.

I am able to get the correct records, but I can't seem to order them properly.

$records = Profile::select(['id', 'name', 'headline', 'entertainer', 'dob', 'slug'])
      ->where('status', 100)
      ->whereHas('User', function($q) {
        $q->where('banned', false);
      })
      ->whereHas('Subscription', function($q) use ($area) {
        $q->where('is_paid', true)
          ->whereNull('cancelled_on')
          ->whereDate('starts_on', '<=', Carbon::now('UTC')->format('Y-m-d H:i:s'))
          ->whereDate('expires_on', '>=', Carbon::now('UTC')->format('Y-m-d H:i:s'))

          // INCLUDE AREA IF IN REQUEST
          ->when($area, function($query) use ($area) {
            $query->whereHas('Areas', function($query) use ($area){
              $query->where('area_id', $area->id);
            });
          });

      })

      ->with(['Subscription' => function($query) use ($area) {
        $query->select(['id', 'model_id', 'model_type', 'plan_id', 'starts_on', 'expires_on'])

          // INCLUDE AREA IF IN REQUEST
        ->when($area, function($query) use ($area) {
          $query->with('Areas', function($query) use ($area) {
            $query->where('area_id', $area->id)
            ->select(['id', 'subscription_id', 'area_id', 'travel', 'start_date', 'end_date']);
          });
        })

          ->with(['Plan' => function($query){
            $query->select('id', 'level', 'vip');
          }]);
      }]);

So these are the results of a query i would paginate

I want the Subscription to be ordered by plan.level DESC

But I cant get it going.

orderBy I cant get it and sortBy after then cant be paginated.

Any ideas?

Thanks,

0 likes
7 replies
jlrdw's avatar

You need to use

->paginate(10);

Replace 10 with your number.

movepixels's avatar

@jlrdw Sorry I did not paste that part. But yes I have

->paginate(25);

But they are not in any order I need. I need them sorted by the subscription.plan.level, desc

Thats where I am stuck

Ben Taylor's avatar

Try using a join statement with the plans table, then the order by should work.

movepixels's avatar

@Ben Taylor tried adding in as suggested

  ->join('plans', function ($join) {
        $join->on('plans.id', '=', 'subscriptions.plan_id');
      })

Results in "Unknown column 'subscription.plan_id' in 'on clause'"]

Lara_Love's avatar
  ->join('plans', function ($join) {
        $join->on('plans.id', '=', 'subscriptions.plan_id');
      })

Results in "Unknown column 'subscription.plan_id' in 'on clause'"]

your relation between table's not correct

Please or to participate in this conversation.