Laravel leftJoin returns null from 2nd table

Question

I have 2 table duty_sheets

centerId | centerName | p1 | p2 | p3 | p4 | ...p22 | examiId
   1     |  xyz       |  1 |  5 |  8 |  7 |    1   |   1
   2     |  abc       |  9 |  1 |  6 |  6 |    1   |   1

and feedback

id | centerId | inspectorId | A  | B | C | examiId
 1 |    1     |     1       |  1 | 5 | 8 |   1
 2 |    2     |     9       |  9 | 1 | 6 |   1

here is my code

$center = DutySheet::select('duty_sheets.centerId', 'duty_sheets.centerName','feedback.id')
        ->leftJoin('feedback', function ($leftJoin) {
        $leftJoin->on('duty_sheets.examId', 'feedback.examId')
            ->where("duty_sheets.centerId", 'feedback.centerId')
            ->where("feedback.inspectorId", 1);
    })
        ->where("duty_sheets.examId", 1)
        ->where("p20", 1)
        ->get();
    dd($center);

to retrieve "All rows from DutySheet where p20 = 1 and dutysheet.examId = 1, and relevant rows from feedback depend on centerId, inspectorId and examId.

The problem is that the query return feedback.id as null while the record exist in feedback table with the ids.

Laravel version = 9

LEFT JOIN returns INNER JOIN rows UNION ALL unmatched left table rows extended by NULLs. Always know what INNER JOIN you want as part of an OUTER JOIN. After a LEFT JOIN a WHERE, INNER JOIN or HAVING that requires a right [sic] table column to be not NULL removes any rows with introduced NULLs, ie leaves only INNER JOIN rows, ie "turns OUTER JOIN into INNER JOIN". You have that. PS This is a faq. But one must pin down via a minimal reproducible example & write many clear, concise & precise phrasings of one's question/problem/goal to search. — philipxy
– philipxy, Commented Jul 26, 2022 at 20:05

Héctor Peredo · Accepted Answer · 2022-07-26 19:52:34Z

1

The problem is in left Join

->where("duty_sheets.centerId", 'feedback.centerId')

This build a where against the value 'feedback.centerId'

duty_sheets.centerId='feedback.centerId'

You need use

   ->on("duty_sheets.centerId",'=', 'feedback.centerId')

Or

 ->whereColumn("duty_sheets.centerId", 'feedback.centerId')

answered Jul 26, 2022 at 19:52

Héctor Peredo

1975 bronze badges

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

1 Comment

Khalil Over a year ago

Thanks a lot. I've read somewhere that ->where can be use in the closure function but didn't mentioned the difference.

Collectives™ on Stack Overflow

Laravel leftJoin returns null from 2nd table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related