How to count and sum inner relational model in laravel

Question

I am building a small application in Laravel where I got stuck with the sum of inner relational data,

I have a model Company which has Many relation associatedProjects and associatedProjects belongs to relation project and project hasOne technicalDescription.

Company Model:

class Company extends Model {

    public function roles()
    {
        return $this->belongsToMany('Noetic\Plugins\Conxn\Models\Variables\Company\Role', 'company_role_relation', 'company_id', 'role_id')->withTimestamps();
    }

    public function specialisations()
    {
        return $this->belongsToMany('Noetic\Plugins\Conxn\Models\Variables\Company\Role', 'company_specialisation_relation', 'company_id', 'specialisation_id')->withTimestamps();
    }

    public function associatedProjects()
    {
        return $this->hasMany('Noetic\Plugins\Conxn\Models\Project\AssociateCompany','company_id','id');
    }

}

AssociateCompany Model:

class AssociateCompany extends Model {

    protected $table = 'project_associate_company';

    protected $fillable = [
        'project_id', 'company_role_id', 'company_specialisation_id', 'company_id', 'link', 'file_name'
    ];

    public function project()
    {
        return $this->belongsTo('Noetic\Plugins\Conxn\Models\Project','project_id','id');
    }

    public function company()
    {
        return $this->belongsTo('Noetic\Plugins\Conxn\Models\Company','company_id','id');
    }

    public function companyRole()
    {
        return $this->belongsTo('Noetic\Plugins\Conxn\Models\Variables\Company\Role',
            'company_role_id','id');
    }

    public function specialisation()
    {
        return $this->belongsTo('Noetic\Plugins\Conxn\Models\Variables\Company\Role',
            'company_specialisation_id','id');
    }
}

Project Model

class Project extends Model {

    protected $fillable = [
        'user_id','koshy_id', 'name', 'slug', 'owner_spv', 'spv_link', 'latitude', 'longitude',
        'landmark', 'city', 'district', 'state', 'pin_code', 'region_id', 'country', 'building_use',
        'sector', 'conxn_id', 'parent_project_id', 'website', 'project_logo', 'tracked', 'verified',
        'code_link', 'status', 'active', 'premium','area'
    ];

    public function technicalDescription()
    {
        return $this->hasOne('Noetic\Plugins\Conxn\Models\Project\TechnicalDescription','project_id','id');
    }

    public function associateCompany()
    {
        return $this->hasMany('Noetic\Plugins\Conxn\Models\Project\AssociateCompany','project_id','id');

    }

}

Now this technicalDescription has fields construction_cost, now I want to first count total number of associatedProject and fetch sum of all the project's construction_cost which is in technicalDescription, some what I have done this code:

$company = Company:: where( 'status', 'saved')
    ->withCount( 'associatedProjects' )
    ->with('associatedProjects.project.technicalDescription')
    ->get()
    ->transform(function ($value)   {
        $value['project_value'] = $value['associatedProjects']->flatten(2)
            ->pluck('project.technicalDescription')->sum('construction_cost');
        return  $value;
    })
    ->sortByDesc('project_value')
    ->forpage( $request->page , 10 );

$next = $request->page+1 ;
$previous =$request->page-1 ? abs($request->page-1):1 ;

I am unable to use paginate over here as laravel collection doesn't have such method, moreover the query logic also doesn't appear accurate.

Any suggestions are welcome. Thanks

Answer 1

You can use a BelongsToMany relationship to get the technicalDescriptions directly:

class Company extends Model {
    public function technicalDescriptions() {
        return $this->belongsToMany(
            'Noetic\Plugins\Conxn\Models\Project\TechnicalDescription',
             'project_associate_company',
             'company_id',
             'project_id',
             null,
             'project_id'
        );
    }
}

$company = Company::where('status', 'saved')
    ->withCount(['technicalDescriptions as project_value' => function($query) {
        $query->select(DB::raw('sum(construction_cost)'));
    }])
    ->orderByDesc('project_value')
    ->paginate();