分组SQL查询中ActiveRecord.size与.count的异常差异

场景还原 #

我在Usage模型中定义了如下scope：

# == Schema Information
# id                :uuid
# service           :string
# organization_id   :uuid
# document_id       :uuid
# created_at        :datetime

class Usage < ApplicationRecord
  belongs_to :document
  
  scope :grouped, -> {
    joins(document: :user)
      .select("COUNT(usages.document_id), usages.service, usages.document_id, documents.subject, MAX(usages.created_at)")
      .group('service', 'documents.subject', 'document_id', "CONCAT(users.first_name, ' ', users.last_name)",  'usages.created_at::date')
      .order("MAX(usages.created_at) DESC")
  }
  
  ...

在Rails控制台执行result = Usage.all.grouped后得到ActiveRecord::Relation结果：

Usage Load (2.5ms)  SELECT COUNT(usages.document_id), usages.service, usages.document_id, documents.subject, MAX(usages.created_at) FROM "usages" INNER JOIN "documents" ON "documents"."id" = "usages"."document_id" INNER JOIN "users" ON "users"."id" = "documents"."user_id" GROUP BY "usages"."service", documents.subject, "usages"."document_id", CONCAT(users.first_name, ' ', users.last_name), usages.created_at::date ORDER BY MAX(usages.created_at) DESC LIMIT $1  [["LIMIT", 11]]
 
=> #<ActiveRecord::Relation [#<Usage id: nil, service: "document", document_id: "f3455d09-0681-46d0-bf2d-54bb3286089b">, #<Usage id: nil, service: "document", document_id: "db3303de-f2ac-4611-8603-da70847e7eeb">, #<Usage id: nil, service: "document", document_id: "642b9fa8-e72e-4bb8-9551-d30f92bf7e67">] 

调用result.size时，执行新的SELECT COUNT(*)查询并返回分组统计哈希：

result.size

(1.7ms)  SELECT COUNT(*) AS count_all, "usages"."service" AS usages_service, documents.subject AS documents_subject, "usages"."document_id" AS usages_document_id, CONCAT(users.first_name, ' ', users.last_name) AS concat_users_first_name_users_last_name, usages.created_at::date AS usages_created_at_date FROM "usages" INNER JOIN "documents" ON "documents"."id" = "usages"."document_id" INNER JOIN "users" ON "users"."id" = "documents"."user_id" GROUP BY "usages"."service", documents.subject, "usages"."document_id", CONCAT(users.first_name, ' ', users.last_name), usages.created_at::date ORDER BY MAX(usages.created_at) DESC

=> {
  ["document", "Alena Cartwright II welcome a Gorgon", "f3455d09-0681-46d0-bf2d-54bb3286089b", "Test Test", Tue, 09 Aug 2022]=>1, 
  ["document", "Thanh Harber Sr. view a Ankylosaurus", "db3303de-f2ac-4611-8603-da70847e7eeb", "Test2 Test", Mon, 08 Aug 2022]=>1,
  ["document", "Rolando Langosh DO bust a Clay Golem", "642b9fa8-e72e-4bb8-9551-d30f92bf7e67", "Test3 Test", Sun, 07 Aug 2022]=>1
}

但调用result.count时，SQL将原查询中的多个字段嵌套进COUNT函数，导致PostgreSQL报错：

result.count

(1.4ms)  SELECT COUNT(COUNT(usages.document_id), usages.service, usages.document_id, documents.subject, MAX(usages.created_at)) AS count_count_usages_document_id_usages_service, "usages"."service" AS usages_service, documents.subject AS documents_subject, "usages"."document_id" AS usages_document_id, CONCAT(users.first_name, ' ', users.last_name) AS concat_users_first_name_users_last_name, usages.created_at::date AS usages_created_at_date FROM "usages" INNER JOIN "documents" ON "documents"."id" = "usages"."document_id" INNER JOIN "users" ON "users"."id" = "documents"."user_id" GROUP BY "usages"."service", documents.subject, "usages"."document_id", CONCAT(users.first_name, ' ', users.last_name), usages.created_at::date ORDER BY MAX(usages.created_at) DESC

Traceback (most recent call last):
        1: from (irb):5

ActiveRecord::StatementInvalid (PG::UndefinedFunction: ERROR:  function count(bigint, character varying, uuid, character varying, timestamp without time zone) does not exist)
LINE 1: SELECT COUNT(COUNT(usages.document_id), usages...

一、size与count方法的核心差异 #

1. size的行为逻辑

   • 若查询结果已加载到内存，size直接返回内存中记录的数量，不会触发新SQL查询；
   • 若结果未加载，size会根据查询是否包含分组逻辑生成对应统计查询：
     • 无分组时，执行SELECT COUNT(*)获取总条数；
     • 有分组时，执行SELECT COUNT(*)结合原分组条件，返回以分组字段为键、分组内记录数为值的哈希。

2. count的行为逻辑

   • count总会触发新的SQL查询，不会复用已加载到内存的结果；
   • 当原查询包含自定义select语句（尤其是聚合函数）时，count会尝试将select中的所有字段包裹进外层COUNT()中生成新聚合查询；
   • 无分组时默认执行SELECT COUNT(*)，有分组时则基于原分组条件对select中的字段再次聚合。

二、count方法嵌套COUNT的原因 #

你的grouped scope中，select语句包含了多个字段和聚合函数：COUNT(usages.document_id), usages.service, usages.document_id, documents.subject, MAX(usages.created_at)。

Rails的count方法在处理含自定义select的分组查询时，会默认把select中的所有内容作为参数传递给外层COUNT()函数，试图对这些字段/聚合结果再次计数。但PostgreSQL不支持接收多个参数的COUNT()函数（COUNT仅能接收单个字段或*），因此直接报错。

简单来说，Rails在这里的逻辑是：既然原查询已通过select指定了返回字段，调用count时就应对这些指定字段计数，但它错误地将所有select内容塞进COUNT()的参数列表，导致SQL语法不符合PostgreSQL要求。

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