Why constraint on join works, but whereRaw clause on inner joined table doesn't?

Question

I want to understand why I get the expected result if I use the constraint I need on the join clause, but it fails if I do a basic inner join and then I attach a where clause with my condition.

This is the code that returns the expected result:

php
$departmentLeaders = \DB::table('employees AS e')
   ->join('departments AS d', 'd.leader_id', '=', 'e.id')
   ->join('business_units AS bu', 'bu.id', '=', 'd.business_unit_id')
   ->where('bu.id', '=', $buId)
   ->select('e.id')
   ->get();

This is the code that doesn't return anything:

php
$departmentLeaders = \DB::table('employees AS e')
   ->join('departments AS d', 'd.id', '=', 'e.department_id')
   ->join('business_units AS bu', 'bu.id', '=', 'd.business_unit_id')
   ->where('bu.id', '=', $buId)
   ->whereRaw('d.leader_id = e.id')
   ->select('e.id')
   ->get();

RAW SQL for query that returns expected result:

sql
select * from `employees` as `e` inner join `departments` as `d` on `d`.`leader_id` = `e`.`id` inner join `business_units` as `bu` on `bu`.`id` = `d`.`business_unit_id` where `bu`.`id` = ?

RAW SQL for query that doesn't return any row:

sql
select * from `employees` as `e` inner join `departments` as `d` on `d`.`id` = `e`.`department_id` inner join `business_units` as `bu` on `bu`.`id` = `d`.`business_unit_id` where `bu`.`id` = ? and d.leader_id = e.id

I've read this post: SQL join: where clause vs. on clause and from my understanding, both queries should return the same result.

score 0 · Answer 1 · answered Jul 19 '19 at 11:22

Please update your query

$departmentLeaders = \DB::table('employees AS e')
->join('departments AS d', 'd.id', '=', 'e.department_id')
->join('business_units AS bu', 'bu.id', '=', 'd.business_unit_id')
->where('bu.id', '=', $buId)
->whereRaw('d.leader_id = e.id')
->select('e.id')
->get();

with

$departmentLeaders = \DB::table('employees AS e')
->join('departments AS d', 'd.id', '=', 'e.department_id')
->join('business_units AS bu', 'bu.id', '=', 'd.business_unit_id')
->where('bu.id', $buId)
->where('d.leader_id', '=', 'e.id')
->select('e.id')
->get();

Why constraint on join works, but whereRaw clause on inner joined table doesn't?

1 Answers1