Access nested related objects with where clause in Laravel using Eloquent

I’m trying to get a list of tasks that are associated with a specific client, but there is no direct relation between a task and a client. I also need to filter those tasks based on task status and project status.

Here are my models:

class Client extends Model
{
    // Fields ['id']
    public function projects()
    {
        return $this->hasMany('AppProject');
    }
}

class Project extends Model
{
    // Fields ['id', 'client_id', 'status']
    public function client()
    {
        return $this->belongsTo('AppClient');
    }
    public function tasks()
    {
        return $this->hasMany('AppTask');
    }
}
class Task extends Model
{
    // Fields ['id', 'project_id', 'status']
    public function project()
    {
        return $this->belongsTo('AppProject');
    }
}

In my controller, I have direct access to Client $client from the request.

The goal is to return a list of tasks which have a status of ‘open’ and which are children of projects owned by the client.

Basically, I want to write something like this:

$client->projects->where('status', 'active')->tasks->where('status', 'open');

And I want to get back what I’d get from this query:

SELECT * FROM tasks AS t 
    WHERE t.status='open' 
    AND t.project_id IN (
        SELECT id FROM projects AS p 
            WHERE p.status='active' 
            AND p.client_id=1
    );

I’ve been able to solve it using Laravel’s Query Builder, but I want a solution that uses Eloquent’s ORM directly.

DB::table('tasks')
    ->join('projects', function($join) use ($client) {
        $join->on('projects.id', '=', 'tasks.project_id')
            ->where('projects.client_id', '=', $client->id);
    })->select('tasks.*')->get();

This seems to be a variation on a common question, but I’ve been unable to solve it with the other answers posted here.

Answer

What you are looking for is whereHas. It lets you query “results based on the existence of a relationship”.

You could try something like:

Task::where('status', 'open')
    ->whereHas('project', function ($query) use ($client) {
        $query->where('client_id', $client->id)
            ->where('status', 'active');
    })
    ->get();

hasManyThrough also applies on this structure:

// In Client.php
public function tasks()
{
    return $this->hasManyThrough(Task::class, Project::class);
}

$client->tasks()
    ->where('projects.status', 'active')
    ->where('tasks.status', 'open')
    ->get();

However not entirely sure if this would work.

Advertisement

Answer