Qiita Teams that are logged in
You are not logged in to any team

Log in to Qiita Team
Community
OrganizationAdvent CalendarQiitadon (β)
Service
Qiita JobsQiita ZineQiita Blog
Help us understand the problem. What is going on with this article?

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

More than 5 years have passed since last update.

開発をしていたら、どうしても複雑な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)
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away