Olajed's avatar

withSum on grandchild relation

Hello.

I have three models with the following hierarchy :

  • User -id
  • Journey
    • id
    • user_id
  • Confirmation
    • id
    • journey_id
    • user_id

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 of course, it is not possible.

Do you know how can I solve this ?

0 likes
3 replies
RoduanKD's avatar

I've been in a similar case. you should try to load the journeys relation separately

// User.php
public function journeys(): HasMany
{
    return $this->hasMany(Journey::class);
}

// Journey.php
public function confirmations(): HasMany
{
    return $this->hasMany(Confirmation::class);
}

// Then you can
User::with('journeys', fn ($q) => $q->withSum('confirmations', 'budget);
mwaqasiu's avatar

You can create an intermediate HasManyThrough relationship between User and Journey by using Confirmation by following these steps:

  1. Define the relationship on the User model.
  2. Define the relationship on the Journey model.
  3. Define the relationship on the Confirmation model.

Here is an example of how to define the relationships on the User and Journey models:

Define the relationships on the User and Journey models

class User extends Model
{
    protected $hasMany = [
        'journeys' => [Journey::class],
    ];
}

class Journey extends Model
{
    protected $hasMany = [
        'confirmations' => [Confirmation::class],
    ];
}

Define the relationship on the Confirmation model

class Confirmation extends Model
{
    protected $belongsTo = [
        'user' => [User::class],
        'journey' => [Journey::class],
    ];
}

Get the sum for a column of the journeys table by going through the confirmations table

$user = User::find(1);

$totalAmount = $user->journeys()
    ->whereHas('confirmations')
    ->sum('total_amount');

This code will first get all of the journeys that the user has confirmed. It will then use the sum() method to get the sum of the total_amount column for all of the journeys.

The output of this code will be the total amount for all of the journeys that the user has confirmed.

Please or to participate in this conversation.