9322


## 設例

例えば、TimeEntryとTimeEntryActivityは1対多の関係で、TimeEntryにはactivity_idという外部キーでTimeEntryActivityと関連しています.

[code]

    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}
    
[/code]

そして、TimeEntryActivityでは、次の通り登録されています.

[code]

    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]]
    => "設計作業"
    
[/code]

ここで、次のようなデータを取り出すにはどのように書いたらいいでしょうか？

[code]

    {"その他"=>1,  "設計作業"=>26, "開発作業"=>4}
    
[/code]

使っているデータベースは開発ではSQLite3を使っていますが、本番環境ではMySQLを使っています.

## 追記

Redmineの一部なんですが、TimeEntryActivityはActiveRecord::Baseではなく、Enumerationでしたが、たぶん関係がないと思います.

TimeEntry

[code]

    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'
      ...
    
[/code]

Enumeration  
EnumerationはActiveRecord::Baseを承継したクラスでした

[code]

    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
      ...
    
[/code]

TimeEntryActivity

[code]

    class TimeEntryActivity < Enumeration
      has_many :time_entries, :foreign_key => 'activity_id'
      ...
    
[/code]

scheme.rb

[code]

    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
    
[/code]

書き忘れていましたが、Railsのバージョンは4.2.1です.  
よろしくお願いします.

## 追記２

基本方針として、JOINして、pluckで抽出すればいいんですね.  
ただ、エラーは解決できませんでした.

[code]

    [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>
    
[/code]

## 追記３ できました！

[code]

    [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}
    
[/code]

## 追記４ できました！！

INNER JOINでいいなら、これでもOKですね！  
また、テーブル名がわからないときは調べなくてもarel_table使えばいいんですね.なるほど  
そして、foreign_keyを設定しているときのjoinsの引数は、idを取ったシンボルを使えるんですね.  
例： foreign_key: :activity_id ==> joins(:activity)

[code]

    [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}
    
[/code]


CC BY-SA 3.0
2015-04-19T03:22:33.097
9296
2015-04-28T22:52:17.027
2015-04-28T22:52:17.027
199
199
question
ruby-on-rails rails-activerecord sql
ActiveRecordで外部キーで GROUP BYして集計するとき関連先のカラムを使ってグループ分けするには？


ちょっと長いですが、こんな感じでどうでしょうか？

[code]

    TimeEntryActivity
      .joins("LEFT OUTER JOIN enumerations ON enumerations.type = 'TimeEntryActivity' AND enumerations.id = time_entries.activity_id")
      .group(:name)
      .order(:name)
      .pluck("enumerations.name, count(time_entries.id)")
      .to_h
    
[/code]

件数が0件の場合も考慮して外部結合を使っています。

## EDIT

上のSQLが間違っていましたので修正しました。

`LEFT OUTER JOIN enumerations WHERE` => `LEFT OUTER JOIN enumerations ON`


CC BY-SA 3.0
2015-04-19T22:24:57.010
9322
2015-04-20T22:01:05.533
2015-04-20T22:01:05.533
85
85
9296
answer


関連名をjoinsして、`group.count`で、上手くいかないでしょうか？  
groupに指定するのは、関連先の'テーブル名.カラム名'となります。arel_tableを使えばテーブル名を分かってなくても使えますが、belongs_to
のforeign_keyを正しく設定しておく必要があります。

[code]

    TimeEntry.joins(:activity).group(TimeEntryActivity.arel_table[:name]).count
    
[/code]

もしくは、

[code]

    TimeEntry.joins(:activity).group('enumerations.name').count
    
[/code]


CC BY-SA 3.0
2015-04-27T06:16:16.787
9588
2015-04-27T06:16:16.787
9508
9296
answer