laravel query problem using join, what is missing?

Question

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!

are you sure the table's name is `teacher` and not `teachers`? — Aless55, May 12 '21 at 05:53
is it possible that there are no results of the inner join? are you sure that there are is a user email that is also a teacher email? — Aless55, May 12 '21 at 05:59
are you sure you want to use inner join instead of left join? — Aless55, May 12 '21 at 05:59
@Aless55, if im gonna use left join. How can I select fields from it. It is same method as join? can you give me example please? — user10182078, May 12 '21 at 06:05

score 2 · Accepted Answer · answered May 12 '21 at 06:04

2

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:

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?

answered May 12 '21 at 06:04

Aless55

2,652
2
15
26

ohhh that makes sense. But can you tell me too how to select that email from different table? – user10182078 May 12 '21 at 06:11
the select should work like in your code teacher.email or what do you want to select? – Aless55 May 12 '21 at 06:16
for example i want to select their both email. Is that possible? – user10182078 May 12 '21 at 06:18
I just tried the whole answers i found.I also select firstname, middlename and lastname in teacher table. And yes, it did select teachers table. But for some reason firstname middlename and lastname of student table becomes null when i getting the returns. It seems to confuse cuz both table have the same fields name such as firstname, lastname, middlename and even email – user10182078 May 12 '21 at 06:21
or should I change fields name that teachers have? – user10182078 May 12 '21 at 06:22
1

no you could do a `selectRaw('teacher.firstname as teacher_firstname, techer.lastname as teacher_lastname')` isntead of the select, where you rename the attributes with the `as`. – Aless55 May 12 '21 at 06:31
1

Thank you very very very much!!!! It works perfectly. Good thing you notice my question! – user10182078 May 12 '21 at 06:40

laravel query problem using join, what is missing?

1 Answers1