前置き？
ビューを作成する前に、railsコントローラにおいて、データーベースの値を取ってくる場合にどのように記述するのか。今回はgroup by してからのカウントした値を取得する場合のRailsの書き方を調べるのに随分苦労したので、残しておく。
前提条件
JavaScriptで作成したタイピングゲームをRuby on Railsにのせようとしている。
集計が必要か？ってなったのは、タイピングした結果をランキング表示させようと思い立った時に、問題別のランキング表示をする前に、問題別にどれだけチャレンジャーがいたのか、っていうのを先に一覧表示した後で、ランキング表示をしようか、と思ったから。
テーブル
| Qfiles |
|---|
| id |
| title |
| category |
| results_count |
| その他 |
| Results |
|---|
| id |
| user_id |
| qfile_id |
| その他 |
出そうとした値
- 「Qfile辺りのResultを残したuser_id数」
- 「Qfile辺りのResultを残した数」
まぁ、単純だしすぐに終わると思いつつ、先にクエリを作成。
Select
results.`qfile_id`,
qfiles.`title`,
count(distinct results.`user_id`),
count(*)
from
results
inner join
qfiles
on results.`qfile_id` = qfiles.`id`
group by
results.`qfile_id`,
qfiles.`title`
;
パンケーキ(Sequel Pro)で実行させて望み通りの値が表示されてることを確認。さあRailsに直すぞ、と意気込む。
調べる
ポイントは３点。
1. テーブルの結合はどう書くんだ？
2. group by はどう書くんだ？
3. countはどう書くんだ？（ついでにdistinctも）
とにかくググって調べたコードをrails cで片っ端から実行。うまく実行できず、よく分からない時は「こう書けば動くんじゃね？」という勘に頼ったテストもした！笑
とりあえずページ保存
Rails controllerに書く記述をRails cでテスト
- テーブル結合はjoinsで行う。この時selectはいらないが、書かなかった場合、主にしたモデルのカラム(Qfiles.*)しか参照できない。
Qfile.joins(:results).select("results.*, qfiles.*").first.id
ここでもう一つ、先頭に記述するモデルは、親子関係の親の方のモデルでないといけない。これは先頭に記述するモデルを実際に変えてみて、サーバーログに表示されるクエリを見てたらわかる。
- 単純カウントは、最後に.count入れるだけ
- distinctカウントは、selectした後に.countを入れて、さらにcountオプション内でdistinctを記述する
Qfile.joins(:results).group("results.qfile_id").select("results.qfile_id").count("distinct results.user_id")
どうやって一文で実行するのか？
色々調べてテストして分かったのは、Countを２ついっぺんには無理じゃね！？ってこと。ひょっとしたら何か方法があるのかもしれないが、もうギブアップした。
カウントの片一方だけなら合わせることができた。
Qfile.joins(:results).group("results.qfile_id").group("qfiles.category").select("results.qfile_id").order("qfiles.id ASC").count("distinct results.user_id")
もう一個のカウントも一緒にやりたいんだよ・・・。
もう面倒い。生クエリ実行できるんじゃね？
find_by_sql で Rails から生 SQL クエリを直接実行する
ここの通りに実行できた。でもあれ？rails cで実行したらカウント要素が入ってなくね？どうゆうこと？？？
Railsのfind_by_sqlで取得できるモデルからは、select句で指定した名前で値が取れる
取得してるのに表示されないんか〜〜〜い！
これが分かればなんとか値を参照できるから、あとはビューに渡すだけ！！！
でもやっぱり生クエリは邪道な気がする
一生懸命探しました。ふとしたきっかけで新しいキーワードを用いてググったところ、ありました。
ふむ。「Qfile辺りのResultを残した数」はこれで保存しときゃいいじゃん。
gem 'counter_culture'を採用。これで作成したカラムが、Qfiles.results_count
ここまでで作成した実行文
Qfile.joins(:results).group("results.qfile_id").group("qfiles.category").group("results_count").select("results.qfile_id").order("qfiles.id ASC").count("distinct results.user_id")
まぁ、なんとか値が出たから使えるんだけども。なんか参照しづらいハッシュ値で取得してしまう。group by の副作用っぽい。
=> {[1, "英語-単語", 4]=>1, [2, "英語-単語", 1]=>1, [4, "英語-単語", 1]=>1, [7, "英語-文章", 4]=>1}
group by 使わなかった時みたいに、@モデル.カラムで参照させて欲しい！！
Rails ActiveRecordでgroup_by countによる集計結果をrelationとして取得する
なるほど、selectにまとめて入力できるのか！！！最終的に以下になった。
Qfile.joins(:results).select("qfiles.id, qfiles.title, qfiles.results_count, COUNT(distinct results.user_id) AS count_distinct_results_user_id").group("results.qfile_id, qfiles.category, results_count").order("qfiles.id ASC")
=> [#<Qfile:0x00007f88586419b8 id: 1, title: "abide - certification 200語", results_count: 4>,
#<Qfile:0x00007f8858641878 id: 2, title: "certify - drill 200語", results_count: 1>,
#<Qfile:0x00007f8858641738 id: 4, title: "induction - painter 200語", results_count: 1>,
#<Qfile:0x00007f88586415d0 id: 7, title: "英語例文 200件 1", results_count: 4>]
上記にdistinct countが含まれてないが、select文中に指定した名前で参照できる。
Qfile.joins・・・・.count_distinct_results_user_id
selectの記述が長くなってしまうのでなんかイヤな書き方ではあるけども、参照時にインデックス番号で指定しなければなくなるよりはマシか？と思った。
外部結合のメモ
Qfile.left_outer_joins(:results).where(user_id: 1).select("qfiles.id, qfiles.title, qfiles.results_count, COUNT(distinct results.user_id) AS count_distinct_results_user_id").group("qfiles.id, qfiles.category, results_count").order("qfiles.id ASC")