Laravel eager loading nested relationships with custom query

Question

I have the code which works, but without eager loading nested relationships.

$projects = Project::with('organization')
        ->leftJoin('stages', 'stages.project_id', '=', 'projects.id')
        ->leftJoin('activities', 'activities.stage_id', '=', 'stages.id')
        ->leftJoin('tasks', 'tasks.activity_id', '=', 'activities.id')
        ->select('projects.*',  DB::raw('SUM(IF(tasks.status = 4, score, 0)) AS score'), 
                                DB::raw('SUM(tasks.score) AS total_score'))
        ->groupBy('projects.id')
        ->get();

I want to do this with eager loading nested relationships, and if I wouldn't have these custom selects (total_score and score), I would do

$projects = Project::with('stages.activities.tasks');

but the problem appears on those custom selects (score and total_score). I tried something like that, but didn't work

$projects = Project::with(['stages', 'activities', 'tasks' => function($q) { 
        $q->select( DB::raw('SUM(IF(tasks.status = 4, score, 0)) AS score'), 
                    DB::raw('SUM(tasks.score) AS total_score')); 
    }])->get();

How would the SQL of this ideally look like? If I'm understanding your question right, it may be more efficient to do this as 2 queries — rpgFANATIC
– rpgFANATIC, Commented Mar 2, 2015 at 1:32

Martin Erpicum · Accepted Answer · 2021-12-19 19:43:30Z

6

Something like that should work:

$projects = $project::with(array('stages' => function($q)
 {
   $q->with(array('activities' => function($q)
   {
     $q->with(array('tasks' => function($q)
     {
        $q->groupBy('tasks.id');
        $q->addselect([
            DB::raw('SUM(IF(tasks.status = 4, score, 0)) AS score'),
            DB::raw('SUM(tasks.score) AS total_score')
            ]);
       }));
     }));
   }))
 ->get();

If you need more argument to your function function($q) use ($arg).

edited Dec 19, 2021 at 19:43

answered Apr 10, 2015 at 5:00

Martin Erpicum

1711 silver badge4 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel eager loading nested relationships with custom query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related