【Rails】ActiveRecordじゃなくて生のSQLを実行する方法（find_by_sql）

Ruby on Railsで生のSQLを実行する方法です。

ちょっと複雑なデータを取得したい時に、Active Recordだと逆に面倒だったりして生のSQLを描きたい場合があります。

その場合はfind_by_sqlを使います。

基本的な使い方

使い方はシンプルでModel.find_by_sql(sql)です。

> sql = 'select * from new_users;'
=> "select * from new_users;"

> NewUser.find_by_sql(sql)
  NewUser Load (5.3ms)  select * from new_users;
=> [#<NewUser:0x00007fd740f61e30 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00>,
 #<NewUser:0x00007fd73d2380b8 id: 2, name: nil, age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:41:35 JST +09:00, updated_at: Mon, 07 Oct 2019 21:41:35 JST +09:00>,
 #<NewUser:0x00007fd741ee7f08 id: 3, name: nil, age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:42:31 JST +09:00, updated_at: Mon, 07 Oct 2019 21:42:31 JST +09:00>,
 #<NewUser:0x00007fd741ee7d78 id: 4, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, updated_at: Mon, 07 Oct 2019 22:03:40 JST +09:00>,
 #<NewUser:0x00007fd741ee7be8 id: 5, name: nil, age: 1, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:22:18 JST +09:00, updated_at: Mon, 07 Oct 2019 22:22:18 JST +09:00>,
 #<NewUser:0x00007fd741ee7a58 id: 6, name: nil, age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:23:25 JST +09:00, updated_at: Mon, 07 Oct 2019 22:23:25 JST +09:00>,
 #<NewUser:0x00007fd741ee78c8 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00>]

動的なパラメーターを渡す

whereなどに動的に値を渡したい場合は、値を設定したい場所を?にし、配列で渡します。

> sql = 'select * from new_users where name = ?;'
=> "select * from new_users where name = ?;"

> NewUser.find_by_sql([sql, 'opiyo'])
  NewUser Load (0.5ms)  select * from new_users where name = 'opiyo';
=> [#<NewUser:0x00007fd742cf1798 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00>,
 #<NewUser:0x00007fd742cf1608 id: 4, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, updated_at: Mon, 07 Oct 2019 22:03:40 JST +09:00>,
 #<NewUser:0x00007fd742cf1478 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00>]

また、シンボル:nameを使ってハッシュ値で渡すことも可能です。

> sql = 'select * from new_users where name = :name;'
=> "select * from new_users where name = :name;"

> NewUser.find_by_sql([sql, { name: 'opiyo' }])
  NewUser Load (1.9ms)  select * from new_users where name = 'opiyo';
=> [#<NewUser:0x00007fd726052c88 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00>,
 #<NewUser:0x00007fd726052af8 id: 4, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, updated_at: Mon, 07 Oct 2019 22:03:40 JST +09:00>,
 #<NewUser:0x00007fd726052968 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00>]

複数ある場合も同様に?やシンボルを使えばokです。

> name = 'opiyo'
=> "opiyo"

> from = Date.current.beginning_of_month
=> Tue, 01 Oct 2019

> to = Date.current.end_of_month
=> Thu, 31 Oct 2019

> sql = "select * from new_users where name = :name and created_at between :from AND :to;"
=> "select * from new_users where name = :name and created_at between :from AND :to;"
[88] pry(main)> NewUser.find_by_sql([sql, { name: name, from: from.to_s, to: to.to_s }])
  NewUser Load (0.5ms)  select * from new_users where name = 'opiyo' and created_at between '2019/10/01' AND '2019/10/31';
=> [#<NewUser:0x00007fd741af0c88 id: 1, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 21:09:45 JST +09:00, updated_at: Mon, 07 Oct 2019 21:09:45 JST +09:00>,
 #<NewUser:0x00007fd741af0878 id: 4, name: "opiyo", age: nil, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:03:40 JST +09:00, updated_at: Mon, 07 Oct 2019 22:03:40 JST +09:00>,
 #<NewUser:0x00007fd741af05a8 id: 7, name: "opiyo", age: 2, gender: nil, birthday: nil, email: nil, created_at: Mon, 07 Oct 2019 22:29:20 JST +09:00, updated_at: Mon, 07 Oct 2019 22:32:12 JST +09:00>]

テーブルを結合するような場合も普通に出来ます。

> sql
=> "select new_users.name, posts.title from new_users join posts on new_users.id = posts.id"

> new_user_posts = NewUser.find_by_sql(sql)
  NewUser Load (0.5ms)  select new_users.name, posts.title from new_users join posts on new_users.id = posts.id
=> [#<NewUser:0x00007fd7421a2bf8 id: nil, name: "opiyo">,
 #<NewUser:0x00007fd7421a29a0 id: nil, name: nil>,
 #<NewUser:0x00007fd7421a2658 id: nil, name: nil>,
 #<NewUser:0x00007fd7421a22e8 id: nil, name: "opiyo">,
 #<NewUser:0x00007fd7421a20e0 id: nil, name: nil>]

> new_user_posts.first
=> #<NewUser:0x00007fd7421a2bf8 id: nil, name: "opiyo">

> new_user_posts.first.attributes
=> {"id"=>nil, "name"=>"opiyo", "title"=>"一番目"}

> new_user_posts.first.name
=> "opiyo"

> new_user_posts.first.title
=> "一番目"

select指定するとパッと見取得できないような感じですが、attributesで確認するとバッチリ取得できてます。

あとは、いつもと同じようにuser.nameとか使えるのでいかように！