求等价于指定原生SQL的Rails ActiveRecord查询实现代码

需要通过Rails ActiveRecord查询所有活跃Provider及其关联的活跃Study数量，要求包含无关联Study的Provider（这部分Provider的Study数量为0）。但现有Rails代码仅返回有关联Study的Provider，结果为[ [4,1], [1,2] ]；而通过原生SQL可得到预期结果[ [3,0], [5,0], [6,0], [4,1], [1,2] ]，现寻求功能等价的ActiveRecord代码。

现有尝试的Rails代码 #

result = Provider.left_joins(:studies)
 .where("studies.active = true AND providers.active = true")
 .group("providers.id")
 .select("providers.id AS 'prov_id', COUNT('studies.id') AS 'study_count'")
 .order("study_count ASC")
result.map { |row| [ row["prov_id"], row["study_count"] ] }

可实现需求的原生SQL #

result = ActiveRecord::Base.connection.execute <<-SQL
  SELECT "providers"."id" AS 'prov_id',
          COUNT(sid) AS 'study_count'
  FROM "providers"
  LEFT OUTER JOIN (SELECT "studies"."id" AS "sid",
                          "studies"."provider_id" AS "spid"
                  FROM "studies"
                  WHERE (studies.active = true))
  ON "spid" = "providers"."id"
  WHERE (providers.active = true)
  GROUP BY "providers"."id"
  ORDER BY study_count ASC
SQL
result.map { |row| [ row["prov_id"], row["study_count"] ] }

模型与Schema #

class Provider < ApplicationRecord
  has_many :studies
end

class Study < ApplicationRecord
  belongs_to :provider
end

# schema.rb
create_table "providers", force: :cascade do |t|
  t.string "name", null: false
  t.boolean "active", default: true, null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
end

create_table "studies", force: :cascade do |t|
  t.integer "provider_id", null: false
  t.boolean "active", default: true, null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.index ["provider_id"], name: "index_studies_on_provider_id"
end

数据表数据 #

providers表 #

studies表 #

预期结果 #

[ [3,0], [5,0], [6,0], [4,1], [1,2] ]

问题根源 #

原代码中，where("studies.active = true AND providers.active = true") 会将左连接（LEFT JOIN） 隐式转换为内连接（INNER JOIN）：当Provider无关联Study时，studies.active 字段为NULL，不满足= true的条件，导致这部分Provider被过滤，无法出现在结果中。

等价ActiveRecord实现 #

以下三种写法均与原生SQL逻辑完全一致，可得到预期结果：

写法1：对应原生SQL的子查询左连接

# 先定义活跃Study的子查询
active_studies = Study.where(active: true).select(:id, :provider_id)

# 左连接子查询并统计
result = Provider.where(active: true)
  .left_joins("LEFT JOIN (#{active_studies.to_sql}) AS active_studies ON active_studies.provider_id = providers.id")
  .group("providers.id")
  .select("providers.id AS prov_id, COUNT(active_studies.id) AS study_count")
  .order("study_count ASC")

result.map { |row| [row["prov_id"], row["study_count"]] }

写法2：直接兼容无关联Study的条件

result = Provider.where(active: true)
  .left_joins(:studies)
  .where("studies.active IS NULL OR studies.active = true")
  .group("providers.id")
  .select("providers.id AS prov_id, COUNT(studies.id) AS study_count")
  .order("study_count ASC")

result.map { |row| [row["prov_id"], row["study_count"]] }

写法3：利用模型Scope优化（推荐）

先在Study模型中定义活跃Scope：

class Study < ApplicationRecord
  belongs_to :provider
  scope :active, -> { where(active: true) }
end

然后通过merge将Scope条件嵌入JOIN子句：

result = Provider.where(active: true)
  .left_joins(:studies)
  .merge(Study.active)
  .group("providers.id")
  .select("providers.id AS prov_id, COUNT(studies.id) AS study_count")
  .order("study_count ASC")

result.map { |row| [row["prov_id"], row["study_count"]] }

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