Query with sum of nested relationship based on reference id (laravel vue)

user_16082057 0
Viewed 98

With models

Customer
id | name | code | ....

Project(One customer can have many projects,One project can have many report)
id | customer_id | name | code | ....

Report (One project can have many report.the report is update each month)
id | project_id | amount | year | month | ....

How can I calculate the sum of column amount latest record of report based on reference customer_id ? I dont know how to make it work.

Any help will be appreciate. I want to display the sum of amount in latest report(latest report based on the reportid) of each project based on customer id.

I have manythrough relationships set.

public function report()
    {
        return $this->hasManyThrough(
            Report::class,
            Project::class,
            'customer_id', 
            'project_id', 
            'id',
            'id' 
        );
    }
laravel vue.js
1 Answers

Adding the following will solve your problem:

In your User.php file:

public function reports()
{
    return $this->hasManyThrough(
        Report::class,
        Project::class,
        'user_id', 
        'project_id', 
        'id',
        'id' 
    );
}

public function projects(){
    return $this->hasMany(Project::class);
}

public function amount(){
    return $this->projects()->get()->sum(function ($project) {
        return $project->latestReport()->amount;
    });
}

In your Project.php file:

public function reports(){
    return $this->hasMany(Report::class);
}

public function latestReport(){
    return $this->reports()->latest()->first();
}

And finally. you could get the sum of amounts of the latest report for each project by calling:

User::first()->amount();
user_97659100
Related