Get sum from nested relationship column

I have the following structure :

class Container extends Model
{
    public function boxes(): hasMany
    {
        return $this->hasMany(Box::class);
    }
    public function letters(): hasManyThrough
    {
        return $this->hasManyThrough(Letter::class, Box::class);
    }
}

class Box extends Model
{
    public function letter(): hasOne
    {
        return $this->hasOne(Letter::class);
    }
}

class Letter extends Model
{
    public function records(): hasMany
    {
        return $this->hasMany(Record::class);
    }
}

The model Record has a integer column amount.

When I query containers like this :

Container::limit(10)->paginate()

How can I add the sum of all records amount ?

tisuchi

2 years ago

Best Answer

Level 70

@devondahon In your Container model, add this relationship:

public function records(): hasManyThrough
    {
        return $this->hasManyThrough(
            Record::class, // The final model we want to reach
            Box::class, // The intermediary model
            'container_id', // Foreign key on the boxes table
            'letter_id', // Foreign key on the records table
            'id', // Local key on the containers table
            'id' // Local key on the boxes table
        );
    }

Then you can access like this way with withSum()

Container::withSum('records', 'amount')->paginate(10);

2 likes

devondahon

2 years ago

Level 3

@tisuchi Thanks very much for your answer. However, it doesn't work in some cases. Maybe when some box or letter or record is missing, then the whole amount is null. Any idea to fix it ?

1 like

tisuchi

2 years ago

Level 70

@devondahon Yes, you are right. My proposed solution should not get data from missing values.

Maybe you can try a manual approach?

$containers = Container::withSum('records', 'amount')->get();

foreach ($containers as $container) {
    $container->records_sum_amount = $container->records_sum_amount ?? 0;
}

Note, I didn't taste it. It might need some tweaking.

1 like

devondahon

2 years ago

Level 3

@tisuchi Thanks again for your answer.

When a box or a letter or a record is missing, withSum('records', 'amount') will return null even if there are other valid values. I noticed the same behavior when using withCount('records'). I tried to fix it by using with such syntax:

withSum(['records' => fn ($query) => $query->where('type', 1)->where('amount', '>', 0) || 0], 'amount')

But the result is still null when a model is missing. Maybe I should tweak this syntax in some way ? Or maybe there's another solution ?

1 like

Please or to participate in this conversation.