如何使用Peewee编写带多表关联、双重count统计的查询语句

问题原因 #

你之前合并查询出现计数相乘，是因为同时join两个独立的一对多关联时，两个关联表的记录会生成笛卡尔积，比如一个Lottery有2个Package、3个Prize，join后会生成2*3=6条记录，直接count的话两个计数都会变成6。下面两种方案都可以优雅解决这个问题：

方案1：子查询预统计（推荐，性能更稳定） #

先分别用子查询统计好两个关联表的计数，再和Lottery主表关联，完全避免笛卡尔积问题，也能兼容没有关联数据的场景，自动返回0：

from peewee import fn, subquery

# 子查询1：统计每个Lottery关联的Package总数
pkg_subq = (Lottery
            .select(Lottery.id, fn.count(Package.id).alias('packages'))
            .join(LotteryPackage)
            .join(Package)
            .group_by(Lottery.id)
            .subquery('pkg_subq'))

# 子查询2：统计每个Lottery关联的Prize总数
prize_subq = (Lottery
              .select(Lottery.id, fn.count(Prize.id).alias('prizes'))
              .join(LotteryPrize)
              .join(Prize)
              .group_by(Lottery.id)
              .subquery('prize_subq'))

# 主查询直接关联预计算的子查询结果
query = (Lottery
         .select(
             Lottery,
             fn.COALESCE(pkg_subq.c.packages, 0).alias('packages'),
             fn.COALESCE(prize_subq.c.prizes, 0).alias('prizes')
         )
         .join(pkg_subq, on=(Lottery.id == pkg_subq.c.id), join_type='LEFT')
         .switch(Lottery)
         .join(prize_subq, on=(Lottery.id == prize_subq.c.id), join_type='LEFT')
         .order_by(Lottery.id)
         .dicts())

lottery = list(query)

方案2：COUNT+DISTINCT（简洁写法，适合小数据量场景） #

直接在count里加DISTINCT去重，避免笛卡尔积导致的计数重复，写法更简短：

query = (Lottery
         .select(
             Lottery,
             fn.COUNT(fn.DISTINCT(Package.id)).alias('packages'),
             fn.COUNT(fn.DISTINCT(Prize.id)).alias('prizes')
         )
         .join(LotteryPackage, join_type='LEFT')
         .join(Package, join_type='LEFT')
         .switch(Lottery)
         .join(LotteryPrize, join_type='LEFT')
         .join(Prize, join_type='LEFT')
         .group_by(Lottery)
         .order_by(Lottery.id)
         .dicts())

lottery = list(query)

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