Laravel query two tables, not showing if id are equal

Asked

Viewed 122 times

0

I have two related tables in the database: Students and student_schedules. At some point, I want to list all the Students that NAY are in student_schedules. I tried to do it like this, but it didn’t work:

$schedules = StudentSchedule::all();
$students = Student::join('student_schedules', 'student_schedules.student_id', '!=', 'students.id')
                        ->select('student_schedules.student_id as idd', 'students.*')
                        ->get();

The image illustrates precisely what: When there is a student id in the student_schedules table, it does not appear in select inserir a descrição da imagem aqui

laravel laravel-eloquent

  • It will be "How to show data that is not in the student_schedules table?"

  • Friend tries the following:

  • If you created the relationship in the model you can use doesntHave. Student::doesntHave('related_name em_model')->get(); But it only works if you set the related_name related_em_model in the model.

  • SQL would be something like this: SELECT * FROM students WHERE student_id NOT IN (SELECT student_id FROM student_schedules);, would have to convert to Laravel’s ORM

  • I edited the question to see if it is better to understand

  • These relationships are boring even have to be testing, but try so ve if right, Students::leftJoin('Students', 'Students.id', '!=', 'student_schedules.student_id')->get();

Show 1 more comment

2 answers

0

Try it this way:

$result = DB::table('Students')->whereNotIn('id', Function($q){ $q->select('student_id')->from('student_schedules'); })->get();

  • Come on, buddy, I just got another crack at this thing. This option Qs worked, but I need to pass the id of the franchise_schedule (->Where('franchise_schedule_id', $id)), which I receive as a parameter in the url. if I pass the id in the hand, it lists correctly, but if I put the $id it gives an undefined error variable: id $Students = DB::table('Students')->whereNotIn('id', Function($q){ $q->select('student_id') ->from('student_schedules') ->Where('franchise_schedule_id', $id) ->get(); }) ->get();

  • Your Where missed = Where('franchise_schedule_id', '=', $id) .

  • The strange thing is that I use this parameter in this same function to search for other items by $id

0


Good night to the comrades who helped me, after trying so hard to achieve using Laravel’s running queries:

$students = DB::select('select * from students where id not in (select student_id from student_schedules where franchise_schedule_id = :id)', ['id' => $id]);

Thank you all

Browser other questions tagged laravel laravel-eloquent

You are not signed in. Login or sign up in order to post.