Help with sorting query for pagination

I have a multi relation query that needs to be sorted by a deep relation and can not seem to get it going.

Model relations: Listing belongsTo Subscription Subscription hasMany Listing Subscription belongsTo Plan

So I query all the Listing(s) that have an active Subscription and the Subscription includes the Plan but i cant get the Listing(s) that are subscribed to higher / lower Plan.level column to be sorted

End goal would b to have all listing(s) with a higher subscription.plan.level to the top and lower subscription.plan.level to the bottom.

The current obj looks like:

"current_page": 1,
  "data": [
    {
      "id": "c47d-11ed",
      "subscription_id": "c40d-11edf",
	.....
      "subscription": {
        "id": "c40d-11ed",
        "plan_id": "7aaa-4ff1",
		.....
        "plan": {
          "id": "7aaa-4ff1",
          "level": 5, // higher the level goes to top, lower to the bottom
			......
        }
      },
    },

I have tried Subscription Model:

public function Plan()
  {
    return $this->belongsTo('App\Models\Plan')->orderBy('level', 'desc');
  }

Listing Model:

public function Subscription()
  {
    return $this->belongsTo('App\Models\Subscription\Subscription')->with(['plan' => function($q){
			$q->orderBy('level', 'desc');
		}]);
  }

Even in the query i built has no difference.

$this->listing->with(['subscription' => function($q) {
        $q->select(['id', 'plan_id', 'starts_on', 'expires_on'])

        ->with(['plan' => function($q) {
          $q->orderBy('level', 'desc')
            ->select(['id', 'alias', 'name', 'level', 'vip', 'managable']);
        }]);

      }])

Any ideas or suggestions would be greatly appreciated.

LaryAI

3 years ago

Level 58

To sort the listings by the level of the subscription plan, you can modify the query in the Listing model to include an orderBy clause on the level column of the subscription plan. Here's an example:

public function Subscription()
{
    return $this->belongsTo('App\Models\Subscription\Subscription')->with('plan');
}

public function scopeWithSubscriptionPlanLevel($query, $order = 'desc')
{
    return $query->join('subscriptions', 'listings.subscription_id', '=', 'subscriptions.id')
        ->join('plans', 'subscriptions.plan_id', '=', 'plans.id')
        ->orderBy('plans.level', $order)
        ->select('listings.*');
}

Then, you can use this scope in your controller to retrieve the listings with the subscription plan level sorted:

$listings = Listing::withSubscriptionPlanLevel()->paginate(10);

This will return a paginated collection of listings sorted by the level of the subscription plan, with the highest level at the top. If you want to sort by the lowest level at the top, you can pass 'asc' as the second parameter to the scope:

$listings = Listing::withSubscriptionPlanLevel('asc')->paginate(10);

Please or to participate in this conversation.