CODEWITHSUNDEEP
phplaravel
user10182078
user10182078

Reputation: 47

laravel query problem using join, what is missing?

first of all Im not good at english and im new using laravel framework. Im developing some school site for thesis purposes. And I think this might be a chance for me to learn laravel since many developers recommends laravel.

back to my problem hehe

This query runs right.

$questions = DB::table('questions')
                            ->select('questions.id','questions.role','questions.postBody', 'questions.classCode', 'questions.postedById', 'questions.created_at', 'classroom.classroomName','classroom.icon','users.picture', 'students.firstName', 'students.middleName', 'students.lastName', 'students.suffix','students.studentNumber')
                            ->join('classroom', 'classroom.classCode', '=', 'questions.classCode')
                            ->join('users', 'users.id', '=', 'questions.postedById')
                            ->join('students', 'students.email' , '=', 'users.email')
                            ->orderBy('questions.created_at', 'DESC')
                            ->skip($request->page - 5)
                            ->take($request->page)
                            ->get();

and when I adding other table ("teacher") i got no returns. This is the example of my query trying to join 'teacher' table and in here, im trying to select email of teacher

 $questions = DB::table('questions')
                            ->select('questions.id','questions.role','questions.postBody', 'questions.classCode', 'questions.postedById', 'questions.created_at', 'classroom.classroomName','classroom.icon','users.picture', 'students.firstName', 'students.middleName', 'students.lastName', 'students.suffix','students.studentNumber','teacher.email')
                            ->join('classroom', 'classroom.classCode', '=', 'questions.classCode')
                            ->join('users', 'users.id', '=', 'questions.postedById')
                            ->join('students', 'students.email' , '=', 'users.email')
                            ->join('teacher', 'teacher.email' , '=', 'users.email')
                            ->orderBy('questions.created_at', 'DESC')
                            ->skip($request->page - 5)
                            ->take($request->page)
                            ->get();

sorry for my bad english.

Can anyone help me? Thank you very much!

Upvotes: 1

Views: 540

Answers (1)

Aless55
Aless55

Reputation: 2709

The problem is that after your first inner join there are only useres left that are also students. Because inner join is taking the cut-set of the joined tables: enter image description here

https://www.codeproject.com/Articles/33052/Visual-Representation-of-SQL-Joins

In the next innerjoin you are trying to get the cut-set of email addresses that are in the students and also teacher table. This will probably always be empty. I recommend that you look up DB joins and I would use leftJoin for your use case.

->leftJoin('students', 'students.email' , '=', 'users.email')
->leftJoin('teacher', 'teacher.email' , '=', 'users.email')

Here are some good answers on the topic: What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

Upvotes: 2

Related Questions