如何使用Laravel Eloquent筛选出拥有活跃Jobs的客户？

Let's fix this properly by filtering at the database level instead of post-processing in PHP—this ensures your pagination works correctly and boosts performance. Here's how to adjust your code step by step:

1. Filter Clients with Active Jobs Using whereHas #

Instead of fetching all clients and then checking for jobs in a loop, use Eloquent's whereHas method to only retrieve clients that have at least one active job. This reduces the initial dataset and keeps your pagination counts accurate.

2. Preload Active Job Count with withCount #

Avoid the N+1 query problem (where you run a separate query for each client's jobs) by using withCount to calculate the number of active jobs directly in the initial query. This adds an attribute to each client model that you can use in your response.

3. Fix Sorting and Pagination Counts #

Update your sort columns to reference the preloaded count, and make sure recordsFiltered reflects the number of filtered clients (not all clients) so pagination works as expected.

Full Modified Code #

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 => 'title', // Corrected to match your client's name column
        2 => 'active_jobs_count', // Updated to use the preloaded count alias
        3 => 'is_enabled',
        4 => 'actions'
    );

    // Base query: only clients with at least one active job
    $query = Client::select('clients.*')
        ->whereHas('jobs', function ($jobQuery) {
            $jobQuery->where('is_active', 1);
        })
        ->withCount(['jobs as active_jobs_count' => function ($q) {
            $q->where('is_active', 1);
        }]);

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

    // Calculate pagination counts
    $recordsTotal = Client::count(); // Total unfiltered clients
    $recordsFiltered = $query->count(); // Total clients matching active job + search criteria

    // Apply sorting and pagination
    $sortColumnName = $sortColumns[$order[0]['column']];
    $query->orderBy($sortColumnName, $order[0]['dir'])
          ->skip($start)
          ->take($length);

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

    $clients = $query->get();
    foreach ($clients as $client) {
        $json['data'][] = [
            $client->id,
            $client->title,
            '<button class="jobs">' . $client->active_jobs_count . ' 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;
}

Key Explanations: #

• whereHas: This adds a condition to the client query that only includes clients with at least one job matching is_active = 1.
• withCount: This calculates the number of active jobs for each client in the initial query, eliminating the need for separate Job::where() calls per client (fixes performance issues from N+1 queries).
• Pagination Counts: recordsTotal stays as the total number of all clients, while recordsFiltered now reflects the count of clients that meet your active job + search criteria—this ensures your table's pagination controls work correctly.
• Sorting: Updated the sortColumns array to use active_jobs_count so sorting by the job count column functions as intended.

Why Your Previous Attempts Failed: #

• Trying count($client->jobs) in the initial query: At that point, $client doesn't exist yet—you're building the query, not fetching models, so this syntax is invalid.
• Filtering in the loop: This removes clients from the final data array, but recordsTotal and recordsFiltered still reflect all clients. Your table uses these counts to calculate pagination, leading to empty pages or incorrect page numbers.

内容的提问来源于stack exchange，提问作者ThurstonLevi