1

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

Improve this question
4
  • You want the sum of construction_cost per company? Commented Sep 16, 2018 at 19:45
  • @JonasStaudenmeir yes. Commented Sep 16, 2018 at 19:46
  • Please post the three relationships. Commented Sep 16, 2018 at 19:51
  • @JonasStaudenmeir Updated! Commented Sep 16, 2018 at 19:57

1 Answer 1

Reset to default
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();
Improve this answer
Sign up to request clarification or add additional context in comments.

15 Comments

I tried this in my code, but I am unable to get response from the controller, No errors is also coming up.
Replace ->paginate() with ->toSql() and post the result of dd($company);.
"select companies.*, (select sum(construction_cost) from project_technical_detail inner join project_associate_company on project_technical_detail.project_id = project_associate_company.project_id where companies.id = project_associate_company.company_id and project_technical_detail.deleted_at is null) as project_value from companies where status = ? and companies.deleted_at is null order by project_value desc"
Does your controller have a return statement?
Yes return response()->json(['data' => $company], 200);
|

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.