Query Builder Building Joins Incorrectly

1

I am attempting to recreate this simple join using the query builder. I have a simple hasMany association between my Users table and my Applicants table. Any user might have many records in the Applicants table and the Applicants table has a job_id I want to filter on.

Straight sequel…

SELECT * from users
join applicants
on users.id = applicants.user_id
WHERE applicants.job_id in (?,?)

That gives me the set of each job a person cares about, and the job_id they cared about.

In the query builder I’m doing this…

$users = $this->Users
              ->find('all')
              ->leftJoinWith('Applicants', 
    		function($q){
    		        $j = [17,18];
    			$q->where(['Applicants.job_id IN' => $j]);
    			 return $q;
    		} 
    	     );

The query builder uses that to create this

SELECT 
  Users.id AS "Users__id", 
  Users.first_name AS "Users__first_name", 
  Users.last_name AS "Users__last_name", 
... etc..
  Users.role AS "Users__role", 
  Users.veteran AS "Users__veteran", 
  Applicants.id AS "Applicants__id", 
  Applicants.job_id AS "Applicants__job_id", 
  Applicants.user_id AS "Applicants__user_id", 
  Applicants.arrival AS "Applicants__arrival", 
  Applicants.departure AS "Applicants__departure" 
FROM 
  users Users 
  LEFT JOIN applicants Applicants ON (
    Applicants.job_id in (17, 18) 
    AND Users.id = (Applicants.user_id)
  )

Which selects everything from the users table and the applicants table (in other words, does a join) and then tries to join it on the applicants table. This essentially creates a union between the users table and the filtered applicant join.

What is happening here, and how can I fix this?

2

The callable for leftJoinWith() lets you define the join conditions. From what you described you want your IN clause in the WHERE so you’d need to do something like:

$query = $this->Users
    ->find('all')
    ->leftJoinWith('Applicants')
    ->where(['Applicants.job_id IN' => $j]);
2 Likes
3

You, Sir, are clearly a damn genius. :smiley:

Many, many thanks. It’s always way more simple than I make it out to be.