370
Help us understand the problem. What are the problem?

More than 5 years have passed since last update.

posted at

ActiveRecordで生SQLを使いたいときに便利なメソッド達

Ruby で DB を扱うちょっとしたスクリプトを書くとき、ActiveRecord で生SQLを使うと色々捗ることが多い。
そのためのメソッドをまとめてみた。

事前準備

establish_connection

DBとのコネクションを確立する。'mysql2', 'postgresql', 'redshift' など様々なアダプタが使える。
以下は接続設定の一例。

[MySQL] (要 mysql2 gem)

require 'active_record'

config = {
  adapter: 'mysql2',
  host: ___hostname___,
  database: ___databasename___,
  port: 3306,
  username: ___username___,
  password: ___password___,
  encoding: 'utf8',
  timeout: 5000,
}

ActiveRecord::Base.establish_connection(config)

[PostgreSQL] (要 pg gem)

require 'active_record'

config = {
  adapter: 'postgresql',
  host: ___hostname___,
  database: ___databasename___,
  port: 5432,
  username: ___username___,
  password: ___password___,
  encoding: 'utf8',
  timeout: 5000,
}

ActiveRecord::Base.establish_connection(config)

[Redshift] (要 pg, activerecord4-redshift-adapter gem)

require 'active_record'

config = {
  adapter: 'redshift',
  host: "#{___clustername___}.#{___something___}.#{___region___}.redshift.amazonaws.com",
  database: ___databasename___,
  port: 5439,
  username: ___username___,
  password: ___password___,
  encoding: 'utf8',
  timeout: 30000,
}

ActiveRecord::Base.establish_connection(config)

作成・更新系

execute

任意のSQLを実行する。(DBアダプタのクエリ実行メソッドをほぼそのまま叩いている)

con = ActiveRecord::Base.connection

con.execute("INSERT INTO users(name, email) VALUES('Joe', 'joe@example.com')")

検索系クエリを実行した場合、Mysql2::Result, PG::Result などDBアダプタ依存のオブジェクトが返る。
そのため、検索系のクエリを使う場合は後述する select_all などの検索系メソッドを使った方が良い。

検索系

select_all

検索クエリ実行結果として、ActiveRecord::Result というオブジェクトを返す。
このオブジェクトに対して to_hashto_ary, to_a でも可)を実行するとHash配列を取得できる。

con = ActiveRecord::Base.connection

result = con.select_all('SELECT name, email FROM users')
result.to_hash
# => [ {"name" => "Joe",   "email" => "joe@example.com"},
#      {"name" => "Alice", "email" => "alice@example.com"},
#      {"name" => "Bob",   "email" => "bob@example.com"} ]

ちなみに ActiveRecord::Result オブジェクトを返すようになったのはバージョン 4.0.0 から。
それ以前のバージョンではHash配列が返っていた。

select_one

検索クエリ実行結果として、最初のレコードの情報をHashオブジェクトで返す。
select_all(...).first のショートカット的なメソッド。

con = ActiveRecord::Base.connection

result = con.select_one('SELECT name, email FROM users')
# => {"name"=>"Joe", "email"=>"joe@example.com"}

select_rows

複数カラムの値の配列を返す。
カラム名はいらなくて値だけ欲しい場合に便利。

con = ActiveRecord::Base.connection

result = con.select_rows('SELECT name, email FROM users')
# => [ ["Joe",   "joe@example.com"],
#      ["Alice", "alice@example.com"],
#      ["Bob",   "bob@example.com" ] ]

select_values

最初のカラムの値の配列を返す。
select_rows(...).map(&:first) のショートカット的なメソッド。

con = ActiveRecord::Base.connection

result = con.select_values('SELECT name FROM users')
# => ["Joe", "Alice", "Bob"]

検索クエリに複数カラムが指定されていても、最初のカラムの値しか取得できないので注意。

con = ActiveRecord::Base.connection

result = con.select_values('SELECT name, email FROM users')
# => ["Joe", "Alice", "Bob"] ← email の値は取得されない

select_value

最初のレコードの最初のカラムの値を返す。
select_one(...).values.first のショートカット的なメソッド。

con = ActiveRecord::Base.connection

result = con.select_value('SELECT name FROM users')
# => "Joe"

プレースホルダ

sanitize_sql_array

protected メソッドなのが玉にキズだが、
send メソッド経由でこのメソッドを使えばプレースホルダを使ったSQL文を作成できる。

sql = ActiveRecord::Base.send(
  :sanitize_sql_array,
  ['SELECT * from users WHERE name=?', "Bob's"]
)
# => "SELECT * from users WHERE name='Bob\\'s'"

sql = ActiveRecord::Base.send(
  :sanitize_sql_array,
  [ 'SELECT * from users WHERE name=:name AND email=:email',
    name: 'Bob', email: 'bob@example.com' ]
)
# => "SELECT * from users WHERE name='Bob' AND email='bob@example.com'"

Array, Range オブジェクトもいい感じに使えるので便利。

ids = [1, 2, 3]
sql = ActiveRecord::Base.send(
  :sanitize_sql_array,
  ['SELECT * from users WHERE id IN (:ids)', ids: ids]
)
# => "SELECT * from users WHERE id IN (1,2,3)"

ids = (3..10)
sql = ActiveRecord::Base.send(
  :sanitize_sql_array,
  ['SELECT * from users WHERE id IN (:ids)', ids: ids]
)
# => "SELECT * from users WHERE id IN (3,4,5,6,7,8,9,10)"

あわせて読みたい

複数 DB のデータや複数 RDBMS を跨いたデータを Ruby の層で扱う場合の Tips:

Register as a new user and use Qiita more conveniently

  1. You can follow users and tags
  2. you can stock useful information
  3. You can make editorial suggestions for articles
What you can do with signing up
370
Help us understand the problem. What are the problem?