Using laravel join query

Question

I'm really getting confused with the join on laravel.

I got a users table and got a students_subjects table, in the students_subjects table I got a subject_id column and a user_id column, I'm trying to get the users list by the user_id at the teachers_subjects table with the same subject_id column at the students_subjects.

I've tried :

$user_id = Auth::user()->id;
$results = DB::table('users')
->join('students_subjects', 'students_subjects.subject_id', '=', 'teachers_subjects.subject_id')
->where('students_subjects.user_id', $user_id)
->get();

but I got some errors... would be great if someone can show me the way it should be done so I can understand how to do the joins work at laravel.

structures:

users table :
- id
- name
- last name

students_subjects :
- subject_id
- user_id (users->id)

teachers_subjects :
- subject_id
- teacher_id (users->id)

You need two different joins. One for students_subjects, and one for teachers_subjects. Right now, you're trying to join students_subjects on a table you haven't yet specified. — aynber
– aynber, Commented Jul 24, 2018 at 18:13
@aynber first thanks for the answer, can you maybe show me an example of doing it in the best way? — mordehi chro
– mordehi chro, Commented Jul 24, 2018 at 18:13

aynber · Accepted Answer · 2018-07-24 18:18:09Z

1

You need to add a join for both tables:

$user_id = Auth::user()->id;
$results = DB::table('users')
->join('students_subjects', 'users.id', '=', 'students_subjects.user_id')
->join('teachers_subjects', 'students_subjects.subject_id', '=', 'teachers_subjects.subject_id')
->where('students_subjects.user_id', $user_id)
->get();

You may have to tweak this to get exactly what you're looking for.

answered Jul 24, 2018 at 18:18

aynber

23.2k9 gold badges57 silver badges69 bronze badges

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

3 Comments

mordehi chro Over a year ago

thanks that's I think I understand how it works now ty so much!

mordehi chro Over a year ago

what if I wanna get the users with the same teacher_id at users->id

aynber Over a year ago

If you mean users -> students_subjects -> teachers_subjects -> users, then you'd just add a third join. However, I'd recommend aliasing the users table on the third join, because it would conflict with the first users. ->join('users AS teacher_user', 'teachers_subjects.user_id', '=', 'teacher_user.id')

siddhesh · Accepted Answer · 2018-07-24 18:20:34Z

0

If you don't know to use laravel eloquent queries then you can execute raw queries using laravel if you are comfortable with it

$cards = DB::select("SELECT * FROM TABLE");

This way you can easily run your queries without any other RND

answered Jul 24, 2018 at 18:20

siddhesh

5631 gold badge9 silver badges16 bronze badges

Collectives™ on Stack Overflow

Using laravel join query

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