Laravel Eloquent get Clients which have active jobs

Question

I'm working on a table which should show clients which have active jobs. I've got a page with all clients and this is working. I'm trying to get a query which is conditional on the client having active jobs. So here is what I have in my dataSource function:

    public function dataSourcejobs(Request $request) {
        $search = $request->query('search', array('value' => '', 'regex' => false));
        $draw   = $request->query('draw', 0);
        $start  = $request->query('start', 0);
        $length = $request->query('length', 25);
        $order  = $request->query('order', array(0, 'desc'));

        $filter = $search['value'];

        $sortColumns = array(
            0 => 'id',
            1 => 'client',
            2 => 'active_jobs',
            3 => 'is_enabled',
            4 => 'actions'
        );

        $query = Client::select( 'clients.*' );

        if (!empty($filter)) {
            $query->where( 'title', 'like', '%'.$filter.'%' );
        }

        $recordsTotal = $query->count();

        $sortColumnName = $sortColumns[$order[0]['column']];

        $query->orderBy($sortColumnName, $order[0]['dir'])
            ->take($length)
            ->skip($start);

        $json = array(
            'draw' => $draw,
            'recordsTotal' => $recordsTotal,
            'recordsFiltered' => $recordsTotal,
            'data' => [],
        );

        $clients = $query->get();

        foreach ($clients as $client) {
            // Get active jobs for this client.
            $jobs = Job::where( 'client_id', $client->id )->where('is_active', 1)->get();
            $json['data'][] = [
                $client->id,
                $client->title,
                '<button class="jobs">' . count($jobs) . ' Jobs</button>',
                ( $client->is_enabled === 1 ) ? 'Yes' : 'No',
                '<a href="/client/' . $client->id . '/edit">' . config( 'ecl.EDIT' ) . '</a> <a href="/client-workload/' . $client->id . '">' . config( 'ecl.WORK' ) . '</a>'
            ];
        }

        return $json;

    }

I've tried to do things like $query = Client::select( 'clients.*' )->count($client->jobs); but this is obviously wrong and errors. I also tried to do this in the loop (by checking the count) but this obvoiusly broke how the pagination works (but did show only clients with active jobs)

I should point out that the function above shown all clients even the ones which have no associated active jobs.

thanks

Answer 1

to get the count along with each client you can use withCount refer to this link https://laravel.com/docs/9.x/eloquent-relationships#counting-related-models

like the following

$query = Client::withCount('jobs');

as for getting the clients who only have jobs you can use whereHas