select_allで複雑なSELECT文を生SQLで取得

開発をしていたら、どうしても複雑なSQLを書く必要がでてくる。あまり使用したくないが、生SQLを書いた方がわかりやすかったりする。なので、生SQLでの取得方法も覚えておくと良いと思う。

生SQLでの取得例

生SQLの取得方法はいくつかあるが、今回はActiveRecord::Base.connection.select_allを使用した場合の例になる。

     sql = 'select
         u.*,
         h.name hoge_name,
         t.name task_name,
         COUNT(*) cnt,
         SUM(CASE WHEN t.completed_at IS NOT NULL THEN 1 ELSE 0 END) task_complete
       from
         user u
       INNER JOIN
         hoge h
       on h.user_id = u.id
　　　　INNER JOIN
         task t
        on t.user_id = u.id'

    # SQL実行
    result = ActiveRecord::Base.connection.select_all(sql)

• カラム名がかぶると上書きされるので、別名にするようにした方がいい。

返り値

result # => #<ActiveRecord::Result:0xdeadbeef>

result.columns
# => ["id", "name", "hoge_name", "task_name", "cnt", "task_complete"]

result.rows
# => [[1, "名前", "hoge名", "タスク名", "10", "5"],
      [2, "名前2", "hoge名2", "タスク名2", "25", "15"],
      ...
     ]

result.to_hash
# => [{"id" => 1, "name" => "名前", "hoge_name" => "hoge名", "task_name" => "タスク名", "cnt" => "10", "task_complete" => "5"},
      {"id" => 2, "name" => "名前2", "hoge_name" => "hoge名2", "task_name" => "タスク名2", "cnt" => "25", "task_complete" => "15"},
      ...
     ]

result.each do |row|
  puts row['name'] + " " + row['task_name']
end

sanitize_sql_arrayを使用する場合

sql = 'select
         u.*,
         h.name hoge_name,
         t.name task_name,
         COUNT(*) cnt,
         SUM(CASE WHEN t.completed_at IS NOT NULL THEN 1 ELSE 0 END) task_complete
       from
         user u
       INNER JOIN
         hoge h
       on h.user_id = u.id
　　　　INNER JOIN
         task t
        on t.user_id = u.id'
      where
         t.id = ?
      and u.name = ?

    # SQL実行
    sanitize_sql = ActiveRecord::Base.send(:sanitize_sql_array, [sql, 1, '名前'])
    result = ActiveRecord::Base.connection.select_all(sanitize_sql)