自社で開発しているRailsアプリでGROUP BYとORDER BYを組み合わせて使いたい場合があったので調べたことを共有したいと思います。

GROUP BYとORDER BYを併用する際の注意点

そもそもGROUP BYとORDER BYを普通に1つのクエリで実行すると思った結果がでません。

例えば以下のようなテーブルからauthor_idごとに一番古い記事のデータを取得するとします。

mysql> select * from entries;
+----+-------+------+-----------+---------------------+
| id | title | body | author_id | created_at          |
+----+-------+------+-----------+---------------------+
|  1 | text  | text |         1 | 2018-01-30 21:05:25 |
|  2 | text  | text |         2 | 2018-01-30 21:05:35 |
|  3 | text  | text |         2 | 2018-01-31 21:05:38 |
|  4 | text  | text |         1 | 2018-01-31 21:05:45 |
+----+-------+------+-----------+---------------------+
4 rows in set (0.00 sec)

GROUP BYとORDER BYを使ってみると思ったような結果が返ってきません。

mysql> select * from entries group by author_id order by created_at desc;
+----+-------+------+-----------+---------------------+
| id | title | body | author_id | created_at          |
+----+-------+------+-----------+---------------------+
|  2 | text  | text |         2 | 2018-01-30 21:05:35 |
|  1 | text  | text |         1 | 2018-01-30 21:05:25 |
+----+-------+------+-----------+---------------------+
2 rows in set (0.00 sec)

Googleで調べたところGROUP BYとORDER BYを併用すると、先にGROUP BYが実行されるため意図した結果とならないようです。

参考：MySQLでGROUP BYとORDER BYを同時に使用する場合に気をつけたいこと | 日記の間 | あかつきのお宿

サブクエリでORDER BYを優先実行

ではどうするかというとサブクエリを使います。

参考：[SQL] 7. サブクエリ 1 | TECHSCORE(テックスコア)

mysql> select * from (select * from entries order by created_at desc) A  group by author_id;
+----+-------+------+-----------+---------------------+
| id | title | body | author_id | created_at          |
+----+-------+------+-----------+---------------------+
|  4 | text  | text |         1 | 2018-01-31 21:05:45 |
|  3 | text  | text |         2 | 2018-01-31 21:05:38 |
+----+-------+------+-----------+---------------------+
2 rows in set (0.01 sec)

期待通りの結果になりました。

Railsでの実現方法

ではここからが本題でRailsでどうやって表現するか。

最初に思いついたのはfind_by_sql で直にSQLを書く方法です。

ただ、これだとActiveRecordの便利な機能が全く使えなくなる（includes など）のでイマイチでした。

そこで色々と調べる内にActiveRecord::QueryMethods にfrom というメソッドがあるということがわかりました。

参考：http://api.rubyonrails.org/v4.2.10/classes/ActiveRecord/QueryMethods.html#method-i-from

今回の例で言うと以下のような形になるかと思います。

require 'active_record'
require 'pp'

config = {
  adapter: 'mysql2',
  host: 'localhost',
  database: 'sample',
  port: 3306,
  username: 'root',
  password: '',
  encoding: 'utf8',
  timeout: 5000,
}

ActiveRecord::Base.establish_connection(config)

class Entry < ActiveRecord::Base
end

entries = Entry.order(created_at: :desc)
pp Entry.group(:author_id).from(entries, :entries)
=>
[#<Entry:0x00007fda0417e3d8
  id: 4,
  title: "text",
  body: "text",
  author_id: 1,
  created_at: 2018-01-31 21:05:45 UTC>,
 #<Entry:0x00007fda04175120
  id: 3,
  title: "text",
  body: "text",
  author_id: 2,
  created_at: 2018-01-31 21:05:38 UTC>]

先に、

entries = Entry.order(created_at: :desc)

の部分でORDER BYによる整列をしてから、

Entry.group(:author_id).from(entries, :entries)

でGROUP BYするという流れになります。

これで思った通りの処理ができるようになりました。

APPENDIX

速度的な所はどうなのだろうと気になって調べてみる以下のような記事を見つけました。

参考：MySQL のサブクエリって、ほんとに遅いの？ ｜ Developers.IO

どうやらMySQLのバージョンが5.6以上であれば、それなりの速度が出るようです。

今回必要になった箇所については、そこまでのデータ件数ではなかったので、とりあえずはこの方法で行く予定ですが、パフォーマンスによっては別の方法を考える必要がでてきそうです。

まとめ

Railsって本当に便利ですよね！皆さんもぜひ上手にRailsを乗りこなして下さい。

中間テーブルに権限属性を持たせて、権限をコントロールするということがよくあります。以下のコードはシンプルな構成例です。

class User < ApplicationRecord
  has_many :memberships, dependent: :destroy
  has_many :accounts, through: :memberships
end

class Account < ApplicationRecord
  has_many :memberships, dependent: :destroy
  has_many :users, through: :memberships
end

# user_id :integer
# account_id :integer
# admin :boolean
class Membership < ApplicationRecord
  belongs_to :user
  belongs_to :account
end

そんな時、今ままでは以下のようにチェックしていました。

class User < ApplicationRecord
  def admin?(account)
    memberships.find_by(account_id: account.id).admin
  end
end

user = User.first
account = user.accounts.first
user.admin?(account)

これだとわざわざ権限をチェックするために、毎回中間テーブルを参照する必要があります。これでは権限チェックのたびにクエリが走って効率が悪いことこの上ありません。

そこで関連テーブルの取得と同時に中間テーブルの権限属性を引っ張ってくることはできないかと考えました。

考えた結果、何ということはない、単純にselect文の中に中間テーブルの属性を設定してあげれば関連先のテーブルで当該属性を取得できました。

accounts = current_user.accounts.select("accounts.*, memberships.admin AS admin")
accounts.first.admin
=> 1

注意する点は中間テーブルから引っ張ってきたbooleanの属性はtrueまたはfalseではなく、1または0となることです。単純にaccount.admin?とするだけであれば、問題なくtrue, falseの判定ができますが中身のデータを使って何かをするようなときには気をつけなくてはいけません。