📝

[Rails][SQL] select_all find_by_sql 生SQL実行メモ

2023/11/23に公開

Rails

生SQL実行するとき用にメモを残しておきます。
だいたい以下みたいな場合で生SQLを実行するか悩むかと思います。。。

クエリ速度が欲しい
複雑なクエリを実行したい（メソッドチェーンだらけになることを回避）
ウィンドウ関数を使うケースで複数レコードを1レコードとして扱いたい

環境

rails 7.0.3

select_all

クエリ結果がArrayで取得したい場合に使用します。

# クエリ実行例
sql = <<~SQL
  SELECT t.id, t.name, t.type, t.created_at AS execute_at
  FROM tasks t
  WHERE
    t.id IN(
      SELECT MAX(t_sub.id)
      FROM tasks t_sub
      INNER JOIN
        users u ON (
          u.id = t_sub.user_id AND u.actice = 1
        )
      WHERE t_sub.created_at < :limit_date
      GROUP BY u.id, t_sub.type
    )
  ORDER BY t.id DESC
SQL
query = ActiveRecord::Base.sanitize_sql_array(
  [query, { limit_date: '2023-12-01 20:00:00' }]
)
results = ActiveRecord::Base.connection.select_all(query)

マッピングが必要な場合はresults_2みたいなイメージでします。

# results_1 レスポンス例
results_1 = results.to_a
[
  {
    id: 1,
    name: "とんこつラーメン1",
    type: "payment",
    execute_at: "XXXX-XX-XX 12:00:00"
  },
  {
    id: 2,
    name: "とんこつラーメン2",
    type: "payment",
    execute_at: "XXXX-XX-XX 12:00:00"
  },
  ...
]

# results_2 レスポンス例
results_2 = results.map { _1[:type] = '支払い' if _1[:type] == 'payment' }
[
  {
    id: 1,
    name: "とんこつラーメン1",
    type: "支払い",
    execute_at: "XXXX-XX-XX 12:00:00"
  },
  {
    id: 2,
    name: "とんこつラーメン2",
    type: "支払い",
    execute_at: "XXXX-XX-XX 12:00:00"
  },
  ...
]

find_by_sql

クエリ結果がActiveRecordインスタンスで取得したい場合に使用します。

sql = <<~SQL
  SELECT t.id, t.name, t.type, t.created_at, t.updated_at
  FROM tasks t
  WHERE
    t.id IN(
      SELECT MAX(t_sub.id)
      FROM tasks t_sub
      INNER JOIN
        users u ON (
          u.id = t_sub.user_id AND u.actice = 1
        )
      WHERE t_sub.created_at < :limit_date
      GROUP BY u.id, t_sub.type
    )
  ORDER BY t.id DESC
SQL
query = ActiveRecord::Base.sanitize_sql_array(
  [query, { limit_date: '2023-12-01 20:00:00' }]
)
tasks = Task.find_by_sql(query)

そしてアソシェーションも紐づいた状態でActiveRecordインスタンスを取得したい場合は以下のように紐付けします。

...
tasks = Task.find_by_sql(query)
ActiveRecord::Associations::Preloader.new(
  records: tasks, associations: %i[user]
).call

SQLインジェクション対策

外部パラメータをクエリ条件にする場合は各サニタイズメソッドを工夫して実行する必要があります。（基本は避けたほうがいいですが…）

環境

select_all

find_by_sql

SQLインジェクション対策

Discussion