ArelでLEFT JOIN + SELECT句

includesを使ってleft joinした時にselect句がうまく動かずpostgresqlで
group byが使えなかったので、arelでやってみる。

親クラス

group.rb

class Group < ActiveRecord::Base
  self.primary_key = :code
  has_many :users, primary_key: :code, foreign_key: :group_code
end

子クラス

user.rb

class User < ActiveRecord::Base
  belongs_to :group, primary_key: :code, foreign_key: :group_code
end

includesとreferencesで取得してみた結果

Group.includes(:users).references(:users).
    select("groups.code , groups.name , avg(age) as avg_age").group("groups.code , groups.name")

=> SQL (0.7ms)  SELECT groups.code , groups.name , avg(age) as avg_age, "groups"."code" AS t0_r0, "groups"."name" AS t0_r1, "groups"."created_at" AS t0_r2, "groups"."updated_at" AS t0_r3, "users"."id" AS t1_r0, "users"."name" AS t1_r1, "users"."age" AS t1_r2, "users"."prefecture" AS t1_r3, "users"."created_at" AS t1_r4, "users"."updated_at" AS t1_r5, "users"."group_code" AS t1_r6 FROM "groups" LEFT OUTER JOIN "users" ON "users"."group_code" = "groups"."code" GROUP BY code

PG::GroupingError: ERROR:  column "users.id" must appear in the GROUP BY clause or be used in an aggregate function

全カラムがselectされ、グルーピングできないと怒られる。

arelを使って取得した結果

group = Group.arel_table
user = User.arel_table

# Arelでjoinの条件を設定
join_condition = group.join(user, Arel::Nodes::OuterJoin).
              on(group[:code].eq(user[:group_code])).join_sources

test = Group.joins(join_condition).group(:prefecture, :code).
            select(group[:code], group[:name], user[:age].average.as('avg_age'))

  Group Load (0.8ms)  SELECT "groups"."code", "groups"."name", AVG("users"."age") AS avg_age FROM "groups" LEFT OUTER JOIN "users" ON "groups"."code" = "users"."group_code" GROUP BY prefecture, code
=> #<ActiveRecord::Relation [#<Group code: "A3", name: "グループ3">, #<Group code: "A1", name: "グループ1">, #<Group code: "A2", name: "グループ2">]>

test[1].avg_age.to_i
=> 21

ややこしいけどなんとか取得できた。