Railsで使用する生SQL
Railsにおいて、ActiveRecordのwhere等では表現できない複雑なクエリを使用してDBのデータを取得する場合、生でSQLを書く必要が出てくると思います。
しかし、AcitiveRecordで生sqlを実行するメソッドは数多くあり、用途によってどれを使用すべきか分からないという方もいるのではないでしょうか。
そこで今回はよく使用されるであろう、execute/select_all/find_by_sqlについて、どのように呼び出すのか、戻り値はなにか、どのような用途で使用すべきかについて説明します。
先に結論を書いておくと
execute -> クエリの実行
select_all -> セレクト文の結果取得
find_by_sql -> ActiveRecordオブジェクトの取得
となります。詳しくは以下ご覧ください
環境
Rails 5.2.3
Ruby 2.6.0
SQL
今回は全てのメソッドで同じSQLを実行します。
StatusテーブルとAccountテーブルをJOINして、それぞれのデータを取得するselect文です。
select s.text, a.username, a.updated_at from statuses s inner join accounts a on a.id = s.account_id where a.id =1
では１つずつ確認します。
execute
概要
executeは、SQLの実行を行うメソッドです。（https://api.rubyonrails.org/v5.2.3/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#method-i-execute）
実行
ActiveRecord::Base.connection.execute(sql)のようにして実行します。
res = ActiveRecord::Base.connection.execute('select s.text, a.username, a.updated_at from statuses s inner join accounts a on a.id = s.account_id where a.id = 1')
(0.6ms) select s.text, a.username, a.updated_at from statuses s inner join accounts a on a.id = s.account_id where a.id = 1
=> #<PG::Result:0x00007f89f93da560 status=PGRES_TUPLES_OK ntuples=5 nfields=3 cmd_tuples=5>
このようにPG::Resultのインスタンスが返ってきました。このインスタンスは使用するDBによって異なりますが、中身としては実行後のstatus情報が入っています。
用途
この返り値から見ても、executeはその名の通り、クエリの実行を目的としており、取得を目的としているものではありません。
そのため、insertやupdate等のクエリを直接実行したい場合に使用するメソッドと言えます。
似たような実行を目的としたメソッドに、create(レコードの作成)やtransaction（トランザクションの実行）などがあります。
（https://api.rubyonrails.org/v5.2.3/files/activerecord/lib/active_record/connection_adapters/abstract/database_statements_rb.html）
select_all
概要
select_allは、実行したSelect文のレコードを取得するメソッドです。
https://api.rubyonrails.org/v5.2.3/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#method-i-select_all
実行
実行する場合は、ActiveRecord::Base.connection.execute(sql)というように、sqlを引数に渡して実行します。
res = ActiveRecord::Base.connection.select_all('select s.text, a.username, a.updated_at from statuses s inner join accounts a on a.id = s.account_id where a.id = 1')
(0.7ms) select s.text, a.username, a.updated_at from statuses s inner join accounts a on a.id = s.account_id where a.id = 1
=> #<ActiveRecord::Result:0x00007f89f93fa310
@column_types=
{"text"=>#<ActiveRecord::Type::Text:0x00007f89f6fa8e80 @limit=nil, @precision=nil, @scale=nil>,
"username"=>#<ActiveModel::Type::String:0x00007f89f6d5ce88 @limit=nil, @precision=nil, @scale=nil>,
"updated_at"=>#<ActiveRecord::ConnectionAdapters::PostgreSQL::OID::DateTime:0x00007f89f6d5c3e8 @limit=nil, @precision=nil, @scale=nil>},
@columns=["text", "username", "updated_at"],
@hash_rows=nil,
@rows=
[["今日から始めるドン", "sobameshi0901", "2019-12-21 11:32:44.065979"],
["何しているドン", "sobameshi0901", "2019-12-21 11:32:44.065979"],
["マストドン！", "sobameshi0901", "2019-12-21 11:32:44.065979"],
ActiveRecord::Result(https://api.rubyonrails.org/classes/ActiveRecord/Result.html
)のインスタンスが返ってきました。
アトリビュートとして、columnsにはsqlのselectで指定したカラムの一覧を配列が、rowsにはレコードの配列が入っています。
また、カラム名をkey、値をvalueとしたハッシュを１件のレコードとした配列を取得することができるto_aメソッドが用意されていたり、Enumerableがincludeされているので、eachやmapなどのメソッドを使用することもできます。
res.to_a
=> [{"text"=>"今日から始めるドン", "username"=>"sobameshi0901", "updated_at"=>"2019-12-21 11:32:44.065979"},
{"text"=>"何しているドン", "username"=>"sobameshi0901", "updated_at"=>"2019-12-21 11:32:44.065979"},
{"text"=>"マストドン！", "username"=>"sobameshi0901", "updated_at"=>"2019-12-21 11:32:44.065979"}
res.each{|a|puts "#{a['username']}:#{a['text']}"}
sobameshi0901:今日から始めるドン
sobameshi0901:何しているドン
sobameshi0901:マストドン！
用途
このように、select文の結果のレコードを扱いやすい形にして返してくれるのがこのselect_allメソッドです。
selectクエリを実行した状態(ActiveRecordを継承した１モデルではなく、様々なテーブルを組み合わせた結果)をそのまま扱いやすい形で欲しいという場合にはこのメソッドが使用できるでしょう。
関連するメソッドにはselect_one(１件だけ取得する)、select_values(selectの最初のカラムのレコードのみを取得する)などがあります。
（https://api.rubyonrails.org/v5.2.3/files/activerecord/lib/active_record/connection_adapters/abstract/database_statements_rb.html）
find_by_sql
概要
find_by_sqlは、ActiveRecordを継承したモデルのインスタンス一覧を取得するメソッドです。
https://api.rubyonrails.org/v5.2.3/classes/ActiveRecord/Querying.html#method-i-find_by_sql
実行
Status.find_by_sql('select s.text, a.username, a.updated_at from statuses s inner join accounts a on a.id = s.account_id where a.id = 1')のように、ActiveRecordモデルから実行します。
res = Status.find_by_sql('select s.text, a.username, a.updated_at from statuses s inner join accounts a on a.id = s.account_id where a.id = 1')
Status Load (0.9ms) select s.text, a.username, a.updated_at from statuses s inner join accounts a on a.id = s.account_id where a.id = 1
=> [#<Status:0x00007f89f2f1b160 id: nil, text: "今日から始めるドン", updated_at: Sat, 21 Dec 2019 11:32:44 UTC +00:00>,
#<Status:0x00007f89f2f1afa8 id: nil, text: "何しているドン", updated_at: Sat, 21 Dec 2019 11:32:44 UTC +00:00>,
#<Status:0x00007f89f2f1a5a8 id: nil, text: "マストドン！", updated_at: Sat, 21 Dec 2019 11:32:44 UTC +00:00>,]
このように、一覧にStatusインスタンスの一覧が配列になって返ってきます。
返ってきたStatusインスタンスは、selectで選択されたカラム（今回はtextとupdated_at）をアトリビュートとして持っています。
selectに入っているカラムでも、Statusモデルのアトリビュートにもともと存在しないものは無視されます。
また、カラム名で判断されるため、今回のようにselectでaccountsテーブルのupdated_at（a.updated_at）を選択していた場合でも、Statusインスタンスのアトリビュートとなってしまうので注意してください。
用途
whereなどでは表現できない/しづらい複雑なクエリで検索した上で、ActiveRecord継承モデルの一覧を取得したい場合に使用するのがよいでしょう。
ちなみに、このメソッド、定義元を見ると、select_allを使用していることが分かります。
その結果をモデルのcolumnに存在するアトリビュートのみを抽出して、インスタンスを生成していました。
def find_by_sql(sql, binds = [], preparable: nil, &block)
result_set = connection.select_all(sanitize_sql(sql), "#{name} Load", binds, preparable: preparable)
column_types = result_set.column_types.dup
attribute_types.each_key { |k| column_types.delete k }
message_bus = ActiveSupport::Notifications.instrumenter
payload = {
record_count: result_set.length,
class_name: name
}
message_bus.instrument("instantiation.active_record", payload) do
result_set.map { |record| instantiate(record, column_types, &block) }
end
end
終わりに
生SQLの実行メソッドはまだまだたくさんありますが、単純に実行するもの、セレクト結果をそのまま取得するもの、ActiveRecord継承モデルのインスタンスを取得するものと、基本的な使い方がわかったのではないでしょうか。
また頻繁に使用するメソッドなどがあれば追加していきます。