複雑なクエリの場合はO/Rマッパーではなく直接SQL書いた方が良いという話

複雑なクエリの場合はO/Rマッパーではなく直接SQL書いた方が良いという話

RailsのO/Rマッパーだけでは複雑な条件取得の場合に役不足のケースがあります。

その場合は直接SQLクエリを作成する方が早いケースがあります。

例）直近１週間で60分以上ゲームしたことがあるユーザーid一覧を取得せよ

この場合はどうすれば良いでしょうか？

0/Rマッパーでも取得はできそうですが、なんどもクエリ発行してDB問い合わせする実装になりそうな気がします。（複雑なので）

今回のケースは直接SQLを書いて、それをDB問い合わせすれば一度のクエリ問い合わせで済みそうです。

SQL

下記のようなSQLクエリを書けば、想定のデータを取得できると思われます。

SELECT user_id FROM
(
    SELECT COUNT(DISTINCT DATE_FORMAT(played_at, '%Y-%m-%d')) AS playing_count, user_id FROM game_rooms
    WHERE started_at BETWEEN now() - INTERVAL 7 DAY + INTERVAL 9 HOUR AND now() - INTERVAL 1 DAY + INTERVAL 9 HOUR
    AND play_time > 60
    GROUP BY user_id
) AS playing_count
WHERE playing_count >= 6;

サブクエリ

(
    SELECT COUNT(DISTINCT DATE_FORMAT(played_at, '%Y-%m-%d')) AS playing_count, user_id FROM game_rooms
    WHERE started_at BETWEEN now() - INTERVAL 7 DAY - INTERVAL 9 HOUR AND now() - INTERVAL 1 DAY - INTERVAL 9 HOUR
    AND play_time > 60
    GROUP BY user_id
) AS playing_count

SELECT COUNT(DISTINCT DATE_FORMAT(played_at, '%Y-%m-%d')) AS playing_count, user_id FROM game_rooms

game_roomsテーブルからselectしたい内容を記載します。

1. プレイ回数(DISTINCTで同じ日にプレイした場合は重複削除する、countを利用するにはgroup_byが必須)
2. ユーザーid(groupで利用するため)

WHERE started_at BETWEEN now() - INTERVAL 7 DAY + INTERVAL 9 HOUR AND now() - INTERVAL 1 DAY + INTERVAL 9 HOUR

直近１週間の絞り込みを行います。+ INTERVAL 9 HOURをしている理由はUTCタイムをTokyo/Asiaタイムに変換する為です。

AND play_time > 60

プレイ時間が60分以上のものに絞り込みを行います。

GROUP BY user_id

user_idでグルーピング化します。(GROUP BYで特定の列をキーにした合計値や平均値などが表示される)

AS playing_count

取得したデータに別名をつける(SELECT文を使ってデータを取得する時、カラム名に対して別名を付けることができます)

WHERE playing_count >= 6;

直近１週間で６日以上ゲームをしているものだけに絞り込む。