I am trying to get the sum of a column of another table that is 2 hasMany relationships away. Ideally I would like to use eloquent and not use raw queries or joins.

Table structure

factions table
id

example rows
1
2
3


islands table
id | faction_id

example rows
1  | 1
2  | 2
3  | 3
4  | 1
5  | 2
6  | 3


shrines table
id | island_id | points (integer)

example rows
1  | 1         | 200
2  | 2         | 100
3  | 3         | 50
4  | 4         | 75
5  | 5         | 200
6  | 6         | 100
7  | 1         | 25
8  | 2         | 40
9  | 3         | 50

Relationships:

Faction hasMany Island

Island hasMany shrines

I would like to be able to get all Factions and get the sum of the points that effectively belong to each faction where the result is something like

$factions = [
             ['id' => 1, 'points' => 300], 
             ['id' => 2, 'points' => 340],
             ['id' => 3, 'points' => 200],
             ...
            ]

so points in the sum of the shrines.points that belongs to the islands that belongs to the faction.

Any help is appreciated and I've found a few posts that talk about similar problems but the problem isn't exactly the same and the solution isn't exactly what I am looking for.

CodePudding user response：

If you add a hasManyThrough relationship on your Faction model, you should be able to use an accessor to do it:

Faction.php

protected $appends = ['points'];

public function shrines(){
        return $this->hasManyThrough(Shrine::class, Island::class);
    }

public function getPointsAttribute(){
    return $this->shrines()->sum('points');
}