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

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

* 事前準備： [establish_connection](#establish_connection)
* 作成・更新系： [execute](#execute)
* 検索系： [select_all](#select_all), [select_one](#select_one), [select_rows](#select_rows), [select_values](#select_values), [select_value](#select_value)
* プレースホルダ： [sanitize_sql_array](#sanitize_sql_array)

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

**[MySQL]** (要 [mysql2](https://rubygems.org/gems/mysql2/) gem)

```ruby:
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](https://rubygems.org/gems/pg/) gem)

```ruby:
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](https://rubygems.org/gems/pg/), [activerecord4-redshift-adapter](https://github.com/aamine/activerecord4-redshift-adapter) gem)

```ruby:
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アダプタのクエリ実行メソッドをほぼそのまま叩いている）

```ruby:
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_hash`（`to_ary`, `to_a` でも可）を実行するとHash配列を取得できる。

```ruby:
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` のショートカット的なメソッド。

```ruby:
con = ActiveRecord::Base.connection

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

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

```ruby:
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)` のショートカット的なメソッド。

```ruby:
con = ActiveRecord::Base.connection

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

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

```ruby:
con = ActiveRecord::Base.connection

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

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

```ruby:
con = ActiveRecord::Base.connection

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

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

```ruby:
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 オブジェクトもいい感じに使えるので便利。

```ruby:
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：

* [Rubyで表形式データから検索値リストによるデータ抽出処理をSQLっぽく書く（インデックス化による高速化も）](http://qiita.com/yut_h1979/items/a19fd37d36e6d34590f7)