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
– Aless55, Commented May 12, 2021 at 5: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
– Aless55, Commented May 12, 2021 at 5:59
are you sure you want to use inner join instead of left join? — Aless55
– Aless55, Commented May 12, 2021 at 5: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
– user10182078, Commented May 12, 2021 at 6:05

Aless55 · Accepted Answer · 2021-05-12 06:04:09Z

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, 2021 at 6:04

Aless55

2,7092 gold badges17 silver badges27 bronze badges

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

7 Comments

user10182078 Over a year ago

ohhh that makes sense. But can you tell me too how to select that email from different table?

Aless55 Over a year ago

the select should work like in your code teacher.email or what do you want to select?

user10182078 Over a year ago

for example i want to select their both email. Is that possible?

Aless55 Over a year ago

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.

user10182078 Over a year ago

Thank you very very very much!!!! It works perfectly. Good thing you notice my question!

|

Collectives™ on Stack Overflow

laravel query problem using join, what is missing?

1 Answer 1

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related