Posted at

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

More than 3 years have passed since last update.

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: