Query Builder Building Joins Incorrectly

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
    		        $j = [17,18];
    			$q->where(['Applicants.job_id IN' => $j]);
    			 return $q;

The query builder uses that to create this

  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" 
  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?

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
    ->where(['Applicants.job_id IN' => $j]);

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

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