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
ややこしいけどなんとか取得できた。