Laravel Eloquent: Create custom attribute from subquery

Question

Modal: Subscription.php

class Subscription extends Model {
   use HasFactory;

   public function payments(): HasMany {
      return $this->hasMany(SubscriptionPayment::class, 'sub_id');
   }

   public function totalDurationDays(): Attribute {
      return Attribute::make(
         get: fn() => $this->payments()->sum('duration_days')
      )->shouldCache();
   }
}

Controller: SubscriptionController.php

    $subs = SharedServer::from('subscriptions AS ss')
       ->select(
          'ss.*',
          DB::raw("(SELECT SUM(duration_days) FROM subscription_payments AS ssp WHERE ssp.sub_id = ss.id) AS total_duration_days"),
       )
       ->paginate();

If total_duration_days is invoked from the custom attribute, Laravel debug bar shows too many queries compared with invoking it directly from the controller.

Is there a way to instruct the query builder to eagerly load total_duration_days custom attribute directly in one query?

Answer 1

The important thing is, what part causes too many queries. If we only talk about custom attribute, then the ->payments() causes the problem. Why? because you are trying to sum the duration of days by querying everytime.

What are the solutions?

1. Eager load the relation and use that.
2. Join tables and use the SQL sum it in the query.
3. Use SubQuery just like you did.

Eager load:

First, you need to use with or load for eager loading, then you need to use the relation without parentheses. Final code:

class Subscription extends Model {
   use HasFactory;

   public function payments(): HasMany {
      return $this->hasMany(SubscriptionPayment::class, 'sub_id');
   }

   public function totalDurationDays(): Attribute {
      return Attribute::make(
         get: fn() => $this->payments->sum('duration_days')
      );
   }
}

And Just like @Tarashdeep-Singh mentioned:

$subs = SharedServer::from('subscriptions AS ss')
    ->with('payments')
    ->paginate();`

But two things keep in mind 1- Remove the parentheses and use payment-> for relation 2- Dont forget to use the eager loading methods like with or load. If you forget any of these two you will have an n+1 problem.

Join:

This is the most performant way to query this data:

$subs = SharedServer::query()
    ->select([
        'subscriptions.*', 
        DB::raw('SUM(subscription_payments.duration_days)')
    ])
    ->join('subscription_payments', 'subscriptions.id', '=', 'subscription_payments.sub_id')
    ->paginate();`

This may not give you the result you want because it depends on how do you want to filter the result, and inner join or left join can be the possible answers. But with this, you have one and only one query so its most performant.

Subquery:

Subqueries is a nice and very clean way to query and to be honest it is easy as well. But the cost is if the subquery is Correlated then it might be less performant than joins. I won't include the example because you already included the subquery in your description. As you can see your subquery relies on the ss (subscriptions) table as a Correlated Subqueries.