Laravel Eloquent中如何同时实现COUNT()与SUM()左连接查询？

刚接触连接查询，尝试为links表左连接另外两张表：

• clicks表：对link_id匹配的clicks.clicks列数据求和（例如link_id=1的两行数据分别为1和4，求和结果应为5）；
• suggestions表：统计link_id的出现次数（例如link_id=1有4行，统计结果应为4）。

使用Laravel Eloquent进行两次左连接时，单独使用任一连接结果正常，但同时使用两个连接时，click_sum数值异常偏高，明显受suggestion_count影响。

测试代码：

use App\Models\Link;
use App\Models\Suggestion;

return Link::select(
  'links.id',
  DB::raw('SUM(clicks.clicks) AS click_sum'), 
  DB::raw('COUNT(suggestions.link_id) AS suggestion_count'),    
)
->leftJoin('clicks', 'clicks.link_id', '=', 'links.id')
->leftJoin('suggestions', 'suggestions.link_id', '=', 'links.id')

->where('links.id', 2872719)
->first();

实际返回结果：

App\Models\Link {#1278
  id: 2872719,
  click_sum: "2340", // 应为4
  suggestion_count: 585, // 该连接影响了click_sum的结果
}

两次左连接会产生笛卡尔积：当links.id=2872719对应1条clicks记录（求和值为4）和585条suggestions记录时，连接后会生成585条重复的clicks数据行。此时SUM(clicks.clicks)实际计算的是4 * 585 = 2340，直接导致结果异常。

方案1：子查询提前计算聚合值 #

分别在子查询中先算出每个link_id的点击总和和建议数，再关联到links表，从根源避免笛卡尔积：

return Link::select(
    'links.id',
    'click_sum',
    'suggestion_count'
)
->leftJoin(
    DB::raw('(SELECT link_id, SUM(clicks) AS click_sum FROM clicks GROUP BY link_id) AS clicks_sub'),
    'clicks_sub.link_id',
    '=',
    'links.id'
)
->leftJoin(
    DB::raw('(SELECT link_id, COUNT(*) AS suggestion_count FROM suggestions GROUP BY link_id) AS suggestions_sub'),
    'suggestions_sub.link_id',
    '=',
    'links.id'
)
->where('links.id', 2872719)
->first();

方案2：利用Eloquent关联简化查询 #

先在Link模型中定义关联关系：

// App\Models\Link.php
public function clicks()
{
    return $this->hasMany(Clicks::class);
}

public function suggestions()
{
    return $this->hasMany(Suggestion::class);
}

然后直接用Laravel提供的withSum和withCount方法查询，代码更简洁且避免连接问题：

return Link::select('id')
    ->withSum('clicks', 'clicks') // 自动生成clicks_sum字段
    ->withCount('suggestions') // 自动生成suggestions_count字段
    ->where('id', 2872719)
    ->first();

方案3：用DISTINCT临时修正（慎用） #

如果不想修改连接逻辑，可以在聚合函数中加入DISTINCT，但仅适用于clicks表无重复求和值的场景：

return Link::select(
    'links.id',
    DB::raw('SUM(clicks.clicks) / COUNT(DISTINCT suggestions.id) AS click_sum'), 
    DB::raw('COUNT(DISTINCT suggestions.id) AS suggestion_count'),
)
->leftJoin('clicks', 'clicks.link_id', '=', 'links.id')
->leftJoin('suggestions', 'suggestions.link_id', '=', 'links.id')
->where('links.id', 2872719)
->first();

⚠️ 注意：此方案依赖重复数据的数量比例，仅作为临时应急方案，优先推荐前两种方案。

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