ActiveRecordで外部キーで GROUP BYして集計するとき関連先のカラムを使ってグループ分けするには？
設例
例えば、TimeEntryとTimeEntryActivityは1対多の関係で、TimeEntryにはactivity_idという外部キーでTimeEntryActivityと関連しています.
TimeEntry.group("activity_id").count
(2.9ms) SELECT COUNT(*) AS count_all, activity_id AS activity_id FROM "time_entries" GROUP BY "time_entries"."activity_id"
=> {1=>1, 8=>26, 9=>4}
そして、TimeEntryActivityでは、次の通り登録されています.
TimeEntryActivity.find(8).name
TimeEntryActivity Load (0.2ms) SELECT "enumerations".* FROM "enumerations" WHERE "enumerations"."type" IN ('TimeEntryActivity') AND "enumerations"."id" = ? ORDER BY "enumerations"."position" ASC LIMIT 1 [["id", 8]]
=> "設計作業"
ここで、次のようなデータを取り出すにはどのように書いたらいいでしょうか？
{"その他"=>1, "設計作業"=>26, "開発作業"=>4}
使っているデータベースは開発ではSQLite3を使っていますが、本番環境ではMySQLを使っています.
追記
Redmineの一部なんですが、TimeEntryActivityはActiveRecord::Baseではなく、Enumerationでしたが、たぶん関係がないと思います.
TimeEntry
class TimeEntry < ActiveRecord::Base
include Redmine::SafeAttributes
# could have used polymorphic association
# project association here allows easy loading of time entries at project level with one database trip
belongs_to :project
belongs_to :issue
belongs_to :user
belongs_to :activity, :class_name => 'TimeEntryActivity'
...
Enumeration
EnumerationはActiveRecord::Baseを承継したクラスでした
class Enumeration < ActiveRecord::Base
include Redmine::SubclassFactory
default_scope lambda {order(:position)}
belongs_to :project
acts_as_list :scope => 'type = \'#{type}\''
acts_as_customizable
acts_as_tree
...
TimeEntryActivity
class TimeEntryActivity < Enumeration
has_many :time_entries, :foreign_key => 'activity_id'
...
scheme.rb
create_table "time_entries", force: :cascade do |t|
t.integer "project_id", null: false
t.integer "user_id", null: false
t.integer "issue_id"
t.float "hours", null: false
t.string "comments", limit: 255
t.integer "activity_id", null: false
t.date "spent_on", null: false
t.integer "tyear", null: false
t.integer "tmonth", null: false
t.integer "tweek", null: false
t.datetime "created_on", null: false
t.datetime "updated_on", null: false
t.integer "ttevent_id"
end
書き忘れていましたが、Railsのバージョンは4.2.1です.
よろしくお願いします.
追記２
基本方針として、JOINして、pluckで抽出すればいいんですね.
ただ、エラーは解決できませんでした.
[34] pry(main)> TimeEntryActivity.joins("LEFT OUTER JOIN enumerations WHERE enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id").group(:name).order(:name).pluck("enumerations.name, count(time_entries.id)").to_h
(0.4ms) SELECT enumerations.name, count(time_entries.id) FROM "enumerations" LEFT OUTER JOIN enumerations WHERE enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id WHERE "enumerations"."type" IN ('TimeEntryActivity') GROUP BY "enumerations"."name" ORDER BY "enumerations"."position" ASC, "enumerations"."name" ASC
SQLite3::SQLException: near "WHERE": syntax error: SELECT enumerations.name, count(time_entries.id) FROM "enumerations" LEFT OUTER JOIN enumerations WHERE enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id WHERE "enumerations"."type" IN ('TimeEntryActivity') GROUP BY "enumerations"."name" ORDER BY "enumerations"."position" ASC, "enumerations"."name" ASC
ActiveRecord::StatementInvalid: SQLite3::SQLException: near "WHERE": syntax error: SELECT enumerations.name, count(time_entries.id) FROM "enumerations" LEFT OUTER JOIN enumerations WHERE enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id WHERE "enumerations"."type" IN ('TimeEntryActivity') GROUP BY "enumerations"."name" ORDER BY "enumerations"."position" ASC, "enumerations"."name" ASC
from /Users/seventh/.rvm/gems/ruby-2.1.5/gems/sqlite3-1.3.10/lib/sqlite3/database.rb:91:in `initialize'
[35] pry(main)> TimeEntryActivity.joins("LEFT OUTER JOIN enumerations WHERE enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id")
TimeEntryActivity Load (0.7ms) SELECT "enumerations".* FROM "enumerations" LEFT OUTER JOIN enumerations WHERE enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id WHERE "enumerations"."type" IN ('TimeEntryActivity') ORDER BY "enumerations"."position" ASC
SQLite3::SQLException: near "WHERE": syntax error: SELECT "enumerations".* FROM "enumerations" LEFT OUTER JOIN enumerations WHERE enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id WHERE "enumerations"."type" IN ('TimeEntryActivity') ORDER BY "enumerations"."position" ASC
=> #<#<Class:#<TimeEntryActivity::ActiveRecord_Relation:0x007f8e70fa0608>>:0x3fc7387d0304>
追記３ できました！
[13] pry(main)> TimeEntryActivity.joins("LEFT OUTER JOIN time_entries ON enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id").group(:name).order(:name).pluck("enumerations.name, count(time_entries.id)").to_h
(3.4ms) SELECT enumerations.name, count(time_entries.id) FROM "enumerations" LEFT OUTER JOIN time_entries ON enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id WHERE "enumerations"."type" IN ('TimeEntryActivity') GROUP BY "enumerations"."name" ORDER BY "enumerations"."position" ASC, "enumerations"."name" ASC
=> {"設計作業"=>42, "開発作業"=>6}
追記４ できました！！
INNER JOINでいいなら、これでもOKですね！
また、テーブル名がわからないときは調べなくてもarel_table使えばいいんですね.なるほど
そして、foreign_keyを設定しているときのjoinsの引数は、idを取ったシンボルを使えるんですね.
例： foreign_key: :activity_id ==> joins(:activity)
[1] pry(main)> TimeEntry.joins(:activity).group(TimeEntryActivity.arel_table[:name]).count
(1.4ms) SELECT COUNT(*) AS count_all, "enumerations"."name" AS enumerations_name FROM "time_entries" INNER JOIN "enumerations" ON "enumerations"."id" = "time_entries"."activity_id" AND "enumerations"."type" IN ('TimeEntryActivity') GROUP BY "enumerations"."name"
=> {"設計作業"=>61, "開発作業"=>9}
[2] pry(main)> TimeEntry.joins(:activity).group('enumerations.name').count
(0.4ms) SELECT COUNT(*) AS count_all, enumerations.name AS enumerations_name FROM "time_entries" INNER JOIN "enumerations" ON "enumerations"."id" = "time_entries"."activity_id" AND "enumerations"."type" IN ('TimeEntryActivity') GROUP BY enumerations.name
=> {"設計作業"=>61, "開発作業"=>9}