Laravel Eloquent Joining Strange query

- 1 answer

Ad

I am using laravel's eloquent in order to query multiple tables in a database. The following query is working. However, I want to add something to the query that will filter out the milestones depending on whether the value of Job table column archived is set to 0 or 1.

$app->get('/dashboard/user', function() {
    global $app;

    $milestones = Milestone::where('user_id', '=', $app->user->user_id)
        ->where('completed_flag', '=', '0')
        ->with('job.businessUnit')
        ->with('user')
        ->orderBy('deadline', 'asc')
        ->get();

    $milestones = $milestones->sort(function($a, $b) {
        $a0 = ($a->milestone_id == $a->job->next_incomplete_milestone_id);
        $b0 = ($b->milestone_id == $b->job->next_incomplete_milestone_id);
        return ( ($a0 == $b0) ? strcmp($a->deadline, $b->deadline) :
                (($a0 == true) ? -1 : 1));
    });
    print json_encode(array(
        'user' => $app->user->toArray(),
        'milestones' => $milestones->toArray()
    ));
});

I've been trying to do something along the lines of each of the following:

    ->join('jobs','jobs.archived','=','0')
//
    ->where('archived','=','0')
//  
    ->with('jobs.archived')
//
    ->with('jobs')
//
    ->where('jobs.archived', '=', '0')
Ad

Answer

Ad

What you are looking for is the whereHas method.

For example:

$milestones = Milestone::whereHas('job', function($query) {
    $query->where('archived','=','0');
})
... // took out rest of code for brevity
->get();

This whereHas example will only retrieve Milestone with a Job that has the archived value of 0.

Regarding the comment above mentioned by @Tezla, I don't think constraining eager loads is what you want because it will still get you Milestone that don't match the requirement. Rather, it will simply return an empty Job collection for the relationship.

Ad
source: stackoverflow.com
Ad