【Rails】SQLで検索する

はじめに

find_by_sql メソッドと select_all メソッドを使用して、独自のSQLでレコードを検索した記録をまとめます。

find_by_sql

実行例

• 引数は文字列だけでなく、配列も指定できる

# 引数が文字列
Employee.find_by_sql("select * from employees em inner join companies co on em.company_id = co.id where em.company_id = 1 and co.deleted =false order by co.id DESC limit 1")
  Employee Load (1.4ms)  select * from employees em inner join companies co on em.company_id = co.id where em.company_id = 1 and co.deleted =false order by co.id DESC limit 1

# 引数が配列
Employee.find_by_sql(["select * from employees em inner join companies co on em.company_id = co.id where em.company_id = ? and co.deleted =? order by co.id DESC limit 1", 1, false])
  Employee Load (2.4ms)  select * from employees em inner join companies co on em.company_id = co.id where em.company_id = 1 and co.deleted =FALSE order by co.id DESC limit 1

結果

• インスタンス化されたオブジェクトの配列を1つ返す
• クエリがレコードを1つしか返さなかった場合にも配列が返される

=> [#<Employee id: 1, company_id: 1, first_name: "太郎", ....>]

select_all

実行例

• 引数は文字列のみで、配列は指定できない
• Rails7までは connection.select_all
• Rails8からは lease_connection.select_all

# 引数が文字列
Employee.connection.select_all("select * from employees em inner join companies co on em.company_id = co.id where em.company_id = 1 and co.deleted =false order by co.id DESC limit 1")
   (1.5ms)  select * from employees em inner join companies co on em.company_id = co.id where em.company_id = 1 and co.deleted =false order by co.id DESC limit 1

# 引数が配列（エラーが発生する）
 Employee.connection.select_all(["select * from employees em inner join companies co on em.company_id = co.id where em.company_id = ? and co.deleted =? order by co.id DESC limit 1", 1, false])
   (0.6ms)  ["select * from employees em inner join companies co on em.company_id = co.id where em.company_id = ? and co.deleted =? order by co.id DESC limit 1", 1, false]
Traceback (most recent call last):
        1: from (irb):26
ActiveRecord::StatementInvalid (TypeError: wrong argument type Array (expected String))

結果

• ActiveRecord::Result クラスのインスタンスを一つ返す

=> #<ActiveRecord::Result:0x000055c6e441b8c8 @columns=["id", "company_id", "first_name", ....], @rows=[[1, 1, "太郎", ...]], @hash_rows=nil, @column_types={}>

結果の補足

• to_aを付けて実行すると各レコードに対応するハッシュを含む配列を一つ返す

Employee.connection.select_all("select * from employees em inner join companies co on em.company_id = co.id where em.company_id = 1 and co.deleted =false order by co.id DESC limit 1").to_a
   (1.5ms)  select * from employees em inner join companies co on em.company_id = co.id where em.company_id = 1 and co.deleted =false order by co.id DESC limit 1

=> [{"id"=>1, "company_id"=>1, "first_name"=>"太郎", ....}]

参考