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)

Answer 1

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

Answer 2

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.