php - 导致 sum() 到多个结果的分组表

Question

也许只是因为真的很晚了，这可能是非常简单的事情，但我的查询有问题:

$lead = Lead::findOrFail($id);
$recipients = Recipient::with('segment')
    ->select('recipients.*',
        DB::raw('coalesce(rulesets.name, "Accept any...") as ruleset_name'),
        DB::raw('coalesce(sum(recipient_leads.leads_left), 0) as leads_left'),
        DB::raw('round(min('.((configuration('location_distance_unit') == 'km') ? '6371' : '3959').' * acos(cos(radians('.$lead->latitude.')) * cos(radians(recipient_locations.latitude)) * cos(radians(recipient_locations.longitude) - radians('.$lead->longitude.')) + sin(radians('.$lead->latitude.')) * sin(radians(recipient_locations.latitude))))) as distance'))
    ->leftJoin('rulesets', 'rulesets.id', '=', 'recipients.ruleset_id')
    ->leftJoin('recipient_leads', 'recipient_leads.recipient_id', '=', 'recipients.id')
    ->leftJoin('recipient_locations', 'recipient_locations.recipient_id', '=', 'recipients.id')
    ->where('recipients.segment_id', $lead->segment_id)
    ->where(function ($query) use ($lead) {
        $query->whereIn('recipients.ruleset_id', $lead->rulesetIds())->orWhereNull('recipients.ruleset_id');
    })
    ->whereRaw('distance <= recipient_locations.distance')
    ->where('recipients.active', true)
    ->where('leads_left', '>', 0)
    ->groupBy('recipients.id');

我的问题是 leads_left 总和似乎乘以了 recipient_locations 的数量。我尝试将 recipient_locations.recipient_id 和 recipient_leads.recipient_id 添加到我的 groupBy 中，但没有效果。

为什么会发生这种情况，我该如何解决？

编辑:这里是原始查询:

select 
    `recipients`.*, 
    coalesce(rulesets.name, "Accept any...") as ruleset_name, 
    coalesce(sum(recipient_leads.leads_left), 0) as leads_left, 
    round(min(6371 * acos(cos(radians(43.8988057)) * cos(radians(recipient_locations.latitude)) * cos(radians(recipient_locations.longitude) - radians(-78.6974833)) + sin(radians(43.8988057)) * sin(radians(recipient_locations.latitude))))) as distance 
from `recipients` 
left join `rulesets` on `rulesets`.`id` = `recipients`.`ruleset_id` 
left join `recipient_leads` on `recipient_leads`.`recipient_id` = `recipients`.`id` 
left join `recipient_locations` on `recipient_locations`.`recipient_id` = `recipients`.`id` 
where `recipients`.`segment_id` = ? and (`recipients`.`ruleset_id` in (?, ?) or `recipients`.`ruleset_id` is null) and distance <= recipient_locations.distance and `recipients`.`active` = ? and `leads_left` > ? 
group by `recipients`.`id`

Answer 1

所以我不会尝试重写整个查询，但你的问题是当你像这样使用多个左连接时，左边的匹配行在右边重复(这就是为什么你的总和是错误)。

如果您在连接之前计算总和，它应该按照您想要的方式工作。

实际上这里有一个以前的答案很好地解释了它:

Using SUM with multiple joins in mysql