Laravel关联表后使用DB::raw()获取去重计数的实现问题

Hey! Let's walk through how to make your join-based count approach work smoothly in Laravel.

First off, your current code is already on the right track—using leftJoin to include all companies even if they have no associated projects, counting distinct project IDs to avoid duplicates, grouping by company ID, and ordering by the count makes perfect sense.

Let's break down the details and optimize it based on your database setup:

1. Basic Working Version (For Non-Strict Database Modes) #

If your MySQL instance has only_full_group_by disabled (common in older setups or non-production environments), your original code should work just fine. Here's a cleaned-up version for clarity:

$query->leftJoin('project_associate_company', 'companies.id', '=', 'project_associate_company.company_id')
      ->select(
          'companies.*',
          DB::raw('COUNT(DISTINCT project_associate_company.project_id) AS project_count')
      )
      ->groupBy('companies.id')
      ->orderBy('project_count', $filter['order']);

2. Strict Mode Compatible Version (Recommended for Production) #

If you're running MySQL with only_full_group_by enabled (the default in modern MySQL versions and Laravel's strict config), you'll need to adjust the select statement to comply with SQL standards. This is because when grouping by companies.id, all non-aggregated columns in your select need to be either part of the group by clause or wrapped in an aggregate function like ANY_VALUE():

Option A: Manually List Company Columns

If your companies table has a manageable number of columns, explicitly list them with ANY_VALUE():

$query->leftJoin('project_associate_company', 'companies.id', '=', 'project_associate_company.company_id')
      ->select(
          DB::raw('ANY_VALUE(companies.id) AS id'),
          DB::raw('ANY_VALUE(companies.name) AS name'),
          DB::raw('ANY_VALUE(companies.email) AS email'),
          // Add all other columns from the companies table here
          DB::raw('COUNT(DISTINCT project_associate_company.project_id) AS project_count')
      )
      ->groupBy('companies.id')
      ->orderBy('project_count', $filter['order']);

Option B: Dynamically Fetch Columns (For Tables With Many Columns)

If you don't want to hardcode every column, use Laravel's schema builder to fetch them dynamically:

// Get all column names from the companies table
$companyColumns = Schema::getColumnListing('companies');

// Wrap each column with ANY_VALUE()
$selectColumns = array_map(function ($column) {
    return DB::raw("ANY_VALUE(companies.$column) AS $column");
}, $companyColumns);

// Add the count column
$selectColumns[] = DB::raw('COUNT(DISTINCT project_associate_company.project_id) AS project_count');

// Build the query
$query->leftJoin('project_associate_company', 'companies.id', '=', 'project_associate_company.company_id')
      ->select($selectColumns)
      ->groupBy('companies.id')
      ->orderBy('project_count', $filter['order']);

3. Performance Optimization (If Applicable) #

If your project_associate_company table uses a unique composite key (company_id + project_id) (which is standard for many-to-many pivot tables), you can remove the DISTINCT keyword from the count—since there's no chance of duplicate project IDs for a single company. This will speed up the query a bit:

DB::raw('COUNT(project_associate_company.project_id) AS project_count')

Why This Works Instead of withCount() #

Your approach is valid when you need to filter or sort directly on the count value in the same query (which you're doing with orderBy), whereas withCount() adds the count as a relation attribute after the main query runs. Using a join lets you integrate the count seamlessly into your select and ordering logic.

Hope this helps you get the exact result you need! 😊

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