複数カラムでGroupした結果をHashでなくActiveRecord::relationで取得したい ## User `(=year)``(:prefecture_id)` 3 Hash User.group(:year, :prefecture_id).having("count_all > 3").order("count_all DESC").count `{[1990, 13]=>4, [1996,2]=>3}` ID ID * * * HashUserIdpluckwhereIdGroup ## 10 user_hash = User.all.group_by{ |user| [ user[:year], user[:prefecture_id] ] } @users = user_hash.values.select{ |user| 3 < user.size }

User.joins(<<~SQL)
INNER JOIN (
#{User.select("year, prefecture_id, count(*) as count_all").group(:year, :prefecture_id).having("count_all > ?", 3).to_sql}
) as year_pref
ON users.year = year_pref.year
AND users.prefecture_id = year_pref.prefecture_id
SQL


User relation ?

# @2020/07/21

sort relation e.g.


User.joins(<<~SQL).order("year_pref.count_all DESC")
INNER JOIN (
#{User.select("year, prefecture_id, count(*) as count_all").group(:year, :prefecture_id).having("count_all > ?", 3).to_sql}
) as year_pref
ON users.year = year_pref.year
AND users.prefecture_id = year_pref.prefecture_id
SQL