Ecto多表关联查询左连接计数被内连接放大如何解决

计数放大是SQL多表JOIN的典型笛卡尔积问题：items与bookmarks、notes均为一对多关联，同时连接两个关联表时，同一个item下的每一条符合条件的bookmark，都会和每一条符合条件的note匹配生成独立结果行。如果某item有2条未删除bookmark、4条已接受note，JOIN后会生成2*4=8行结果，此时直接count(bookmark)会统计所有行内的bookmark记录，自然得到放大后的错误值8，而非真实值2。

以下方案均适配ecto_sql 3.7版本，可根据业务场景选择：

方案1：计数加去重（改动成本最低） #

给count加上distinct去重逻辑，基于bookmark的唯一主键统计，即可消除笛卡尔积带来的重复计数问题：

from item in Item,
  where: item.finished == true,
  left_join: bookmark in assoc(item, :bookmarks),
  on: bookmark.item_id == item.id and bookmark.deleted == false,
  inner_join: note in assoc(item, :notes),
  on: note.accepted == true,
  group_by: item.id,
  select_merge: %{bookmark_count: count(bookmark.id, :distinct)}

注意：必须添加group_by: item.id，否则在Postgres严格SQL模式下会触发语法错误，非严格模式下返回的聚合结果也不可控。

方案2：用EXISTS子查询过滤notes（性能最优） #

你inner join notes的核心需求只是筛选「存在至少一条已接受note的item」，不需要把notes的行拉到结果集中。用exists子查询做过滤完全不会产生笛卡尔积，也不会干扰bookmark的计数，数据量大时性能远高于直接多表JOIN：

from item in Item,
  where: item.finished == true,
  left_join: bookmark in assoc(item, :bookmarks),
  on: bookmark.item_id == item.id and bookmark.deleted == false,
  where:
    exists(
      from note in assoc(item, :notes),
        where: note.accepted == true,
        select: 1
    ),
  group_by: item.id,
  select_merge: %{bookmark_count: count(bookmark.id)}

该方案没有JOIN notes产生冗余行，不需要加distinct即可得到准确计数。

方案3：预聚合bookmark计数（多关联场景最稳妥） #

如果后续还要JOIN更多一对多关联做统计，可以先把bookmark计数做成独立子查询预计算完成，再关联到主查询，后续不管JOIN多少其他表，都不会影响已经算好的计数值：

# 预聚合每个item的未删除bookmark数量
bookmark_count_query =
  from b in Bookmark,
    where: b.deleted == false,
    group_by: b.item_id,
    select: %{item_id: b.item_id, bookmark_count: count(b.id)}

from item in Item,
  where: item.finished == true,
  left_join: bc in subquery(bookmark_count_query),
  on: bc.item_id == item.id,
  inner_join: note in assoc(item, :notes),
  on: note.accepted == true,
  group_by: [item.id, bc.bookmark_count],
  select_merge: %{bookmark_count: coalesce(bc.bookmark_count, 0)}

此处用coalesce处理无未删除bookmark的item，将关联得到的null值转为0，符合常规计数预期。

• 仅需过滤存在已接受note的item、不需要返回note字段：优先选方案2，性能最优，无冗余结果行生成
• 已完成多表JOIN逻辑、需要返回note相关字段、希望最小改动修复问题：选方案1
• 后续需要关联更多一对多表做复杂聚合统计：选方案3，预聚合逻辑完全不受后续JOIN影响，稳定性最高

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