using eloquent model property in subquery of with function

Question

My question may be confuse because I've started learning Laravel.

Can I use eloquent model property in subquery of with() function?

I have classes, students, and grades tables. I wish to get

$classes = [{id:1, name:'maths', students:[{id:2, name: john,
grade:B},{id:1, name: Mac, grade:C}]}, {id:2, name:'physics',
students:[{id:2, name: john, grade:null},{id:1, name: Mac,
grade:null}]}]

for example:

        $classes = Class::with(['students' => function($query){ 
        ->leftJoin('grades', 'grades.student_id', '=', 'students.id')
        }])->get()->toJSON();

This query doesn't select correct grades of related classes. I wish to use class::id = grades.class_id in join condition.

I use leftJoin because some class may not have released its grade.

Marcin Nabiałek · Accepted Answer · 2015-05-03 18:04:02Z

0

You need to do it this way:

 $classes = Class::with(['students' => function($query){ 
        ->leftJoin('grades', function($join) {
             $join->on('grades.student_id', '=', 'students.id');
             $join->on('grades.class_id', '=', 'class.id');
          })
        }])->get()->toJSON();

answered May 3, 2015 at 18:04

Marcin Nabiałek

112k43 gold badges271 silver badges300 bronze badges

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

3 Comments

Ryan Exlay Over a year ago

Hi Marcin, thank for your help, but I aldy tried it. It result this error SQLSTATE[HY000]: General error: 1 no such column: class.id

lukasgeiter Over a year ago

@RyanExlay It should probably be classes.id (or whatever your table name for the Class model is)

Ryan Exlay Over a year ago

@lukasgeiter, same error landed. Anyway, thank for your help. I can't call class property in students. I am trying to use hasManyThrough relations student->class->grade relations.

Ryan Exlay · Accepted Answer · 2015-05-03 18:53:34Z

0

Finally I got solutions, even though, not best solution but it worked. Thank @Marcin Nabialek and @lukasgeiter. It is just simply to through pivot table class_student.

 $classes = Class::with(['students' => function($query){ 
        $query->leftJoin('grades', function($join) {
             $join->on('grades.student_id', '=', 'students.id');
             $join->on('students.id', '=', 'class_student.student_id');
             $join->on('class_student.class_id', '=', 'grades.class_id');
          })
        }])->get()->toJSON();

It took too much time bcoz passing through many join tables.

Hoping to get better solutions.

answered May 3, 2015 at 18:53

Ryan Exlay

3611 gold badge3 silver badges12 bronze badges

Collectives™ on Stack Overflow

using eloquent model property in subquery of with function

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related