Eloquent query builder get only items which have child items-CodePudding

So I am trying to get a list of all active clients that have active jobs but I'm at a loss of how to accomplish this. So here is what I have...

$query = Client::select( 'clients.*' )->where( 'is_enabled', 1 )->activeJobs();

Which throws an error

Call to undefined method Illuminate\Database\Eloquent\Builder::activeJobs() in my client model i have the activeJobs() function as follows:

    public function activeJobs()
    {
        return $this->hasMany( Job::class )->where( 'is_active', 1 );
    }

Just to explain what I am after with my query in words, I'm trying to get a collection of all active client items (determined by is_enabled = 1) which have 1 or more active jobs (determined by is_active = 1)

thanks

CodePudding user response：

What you are looking for is has/whereHas.

So, what you should do is

    $query = Client::select('clients.*')
                   ->whereHas('jobs', fn($query) => $query->where('is_active', 1))
                   ->where('is_enabled', 1)
                   ->get();

This will return you a collection of Clients whose Job's is_active column is 1

CodePudding user response：

Try this

$query = Client::select('clients.*')
    ->where('is_enabled', 1)
    ->whereHas('activeJobs', function ($query) {
        $query->where('is_active', 1);
    })
    ->get();

CodePudding user response：

Try this

$query = Client::has('activeJobs', '>', 1)->get();

CodePudding user response：

Try This:

$query = Client::with('activeJobs')->select( 'clients.*' )->where( 'is_enabled', 1 );

This will return the clients with their relationship(Active Jobs), what you are doing in the code is trying to grab relationship data from collection..

You can either grab the clients, then foreach $client->activeJobs;

or use with which wil return it in query level.

CodePudding user response：

Get active jobs and then collect related users:

$cliensWithActiveJobs = Job::with('client')
    ->where('is_active', 1)
    ->get(['id', 'client_id'])
    ->map(fn ($job) => $job->client)
    ->unique('id');