Laravel query builder add complex query result

Question

I have three models with the following hierarchy :

• User
  • id
  • ....some other properties
• Journey
  • id
  • user_id
  • budget
  • ....some other properties
• Confirmation
  • id
  • journey_id
  • user_id
  • ....some other properties

I have a HasMany from User to Journey, a HasMany from Journey to Confirmation.

I want to get the sum for a column of the journeys table by going through the confirmations table but I cannot create an intermediate HasManyThrough relation between User and Journey by using Confirmation.

I have tried to do

public function journeysMade(): HasManyThrough
{
    return $this->hasManyThrough(Journey::class, Confirmation::class);
}

// And after,
User::with(...)->withSum('journeysMade','budget')

But it was not possible because the relations are not adapted.

With hindsight, the sql query I want to translate would look like

select coalesce(sum(journeys.budget), 0) as income
from journeys
    inner join confirmations c on journeys.id = c.journey_id
where c.user_id = ? and c.status = 'finalized';

How can I implement this query considering how I will use my query builder :

$driversQueryBuilder = User::with(['profile', 'addresses']); // Here
$pageSize = $request->input('pageSize', self::DEFAULT_PAGE_SIZE);
$pageNumber = $request->input('pageNumber', self::DEFAULT_PAGE_NUMBER);
$driversPaginator = (new UserFilterService($driversQueryBuilder))
        ->withStatus(Profile::STATUS_DRIVER)
        ->withCountry($request->input('country'))
        ->withSex($request->input('sex'))
        ->withActive($request->has('active') ? $request->boolean('active') : null)
        ->get()
        ->paginate(perPage: $pageSize, page: $pageNumber);
return response()->json(['data' => $driversPaginator]);

The reason why I want to get a builder is because UserFilterService expects a Illuminate\Database\Eloquent\Builder.

Do you have any idea about how I can solve this problem ?

Answer 1

Not 100% sure what exactly you want to sum, but I think you need the following query

$user->whereHas('journeys', function($query) {
  $query->whereHas('confirmations', function($subQuery) {
    $subQuery->sum('budget);
  }
});

If you the above query isn't summing the budget you need, you just add another layer of abstraction with whereHas methods to get exactly what you need. Hope this helps!

EDIT:

$user->whereHas('confirmations', function($q) {
  $q->withSum('journeys', 'budget')->journeys_sum_budget;
}