Laravel query join when empty-CodePudding

I'm trying in vain to get a list of jobs that have no tasks assigned to them but I'm completely lost.

$jobs = Job::select( 'jobs.*' )
    ->where( 'is_active', '=', 1 )
    ->join( 'tasks', 'jobs.number', '=', 'tasks.job_number' )
    ->get();

This is doing the opposite of what i need and returning jobs that have tasks, further it is repeating the job for each task. So how can I get the jobs that have no tasks?

CodePudding user response：

You could add whereNull clause for your query and change JOIN to LEFT JOIN.

$jobs = Job::select( 'jobs.*' )
    ->where( 'is_active', '=', 1 )
    ->leftJoin( 'tasks', 'jobs.number', '=', 'tasks.job_number' )
    ->whereNull( 'tasks.job_number' )
    ->get();

CodePudding user response：

I worked it out by reading the manual...

$jobs = Job::select( 'jobs.*' )
    ->where( 'is_active', '=', 1 )
    ->whereDoesntHave( 'tasks' )
    ->get();

CodePudding user response：

SELECT jobs.*
FROM jobs
WHERE jobs.number NOT IN (SELECT job_number FROM tasks)

DB Facade

DB::table('jobs')
->select('jobs.*')
->whereIn('jobs.number',(function ($query) {
    $query->from('tasks')
        ->select('job_number');
}
->get();

Model

Job::select('jobs.*')
->whereIn('jobs.number',(function ($query) {
    $query->from('tasks')
        ->select('job_number');
}
->get();

Depending on how you defined your models (relationships) you could even use whereDoesNotHave()