如何从关联的Client与Document表返回含多COUNT字段的结果集

问题背景 #

现有两张关联表：

• Client表：字段id、name
• Document表：字段id、name、expiration_date（格式YYYY-MM-DD）、client_id
• 关联关系：Client与Document是一对多

需求是查询每个客户，返回以下字段：
id（客户ID）、name（客户名称）、documents_count（该客户的总文档数）、expired（已过期的文档数量）、thirty_days_expired（30天内即将过期的文档数量）

当前已实现前三个字段，但尝试统计expired时返回的是布尔值而非数字，需要修正。

现有代码：

Client.all.left_joins(:documents).group(:id).select('clients.*, COUNT(documents.id) AS documents_count')

尝试的错误代码：

Client.all.left_joins(:documents).group(:id).select("clients.*, COUNT(documents.id) AS documents_count, COUNT(documents.expiration < #{Date.today}) AS expired")

解决方案 #

错误原因 #

COUNT()函数会统计所有非NULL值的数量，而documents.expiration < #{Date.today}在SQL中返回的是布尔值（true/false），这两个值都属于非NULL，因此最终统计的是所有关联文档的数量，而非仅过期的数量。

正确实现 #

需要用SUM()结合CASE WHEN来实现条件统计，同时使用参数化查询避免SQL注入：

Client.left_joins(:documents)
      .group(:id)
      .select(
        "clients.*",
        "COUNT(documents.id) AS documents_count",
        "SUM(CASE WHEN documents.expiration_date < ? THEN 1 ELSE 0 END) AS expired",
        "SUM(CASE WHEN documents.expiration_date BETWEEN ? AND ? THEN 1 ELSE 0 END) AS thirty_days_expired"
      )
      .bind_params(Date.today, Date.today, Date.today + 30.days)

代码说明 #

1. COUNT(documents.id) AS documents_count：统计客户关联的总文档数，LEFT JOIN中文档不存在时documents.id为NULL，会被COUNT忽略，结果为0。
2. SUM(CASE WHEN documents.expiration_date < ? THEN 1 ELSE 0 END) AS expired：对每个文档判断是否已过期（过期日期早于今天），符合条件则加1，否则加0，最终SUM得到过期文档的总数。
3. SUM(CASE WHEN documents.expiration_date BETWEEN ? AND ? THEN 1 ELSE 0 END) AS thirty_days_expired：判断文档过期日期是否在今天到未来30天之间，同样通过SUM统计符合条件的数量。
4. 参数化查询：用bind_params传递日期参数，避免直接字符串插值带来的SQL注入风险，同时适配不同数据库的日期格式。

补充说明 #

如果客户没有关联任何文档，documents_count、expired、thirty_days_expired都会返回0，完全符合需求。

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