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.

Related questions:

laravel orm : where condition on table -> related table -> related table

Laravel nested relationships

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.