Help us understand the problem. What is going on with this article?

SQL の記述に ERB を使う

More than 3 years have passed since last update.

はじめに

Rails などで Active Record を使っていても生の SQL を記述したいケースはままあります。その際に SQL ファイル内に変数や条件分岐を埋め込めると便利です。そこで、今回 ERB テンプレートを使って実現してみました。

特定の条件下のみ WHERE 句を使うというケースを想定しています。

方法 1: ERB を使う

sample1.sql.erb
SELECT
  characters.name,
  animes.title
FROM
  characters
  INNER JOIN anime_characters
    ON anime_characters.character_id = characters.id
  INNER JOIN animes
    ON animes.id = anime_characters.anime_id
<% if defined?(:title) %>
WHERE
  animes.title = '<%= title %>'
<% end %>
;
require 'erb'

# 第 3 引数を指定しないと <%= %> の行が空行として出力されてしまう。
template1 = ERB.new(File.read('sample1.sql.erb'), nil, '<>')

puts(template1.result(binding))
#=> NameError: undefined local variable or method `title' for main:Object

title = '偽物語'
puts(template1.result(binding)) # 出力結果は stdout2

title = nil
puts(template1.result(binding)) # 出力結果は stdout1
stdout1
SELECT
  characters.name,
  animes.title
FROM
  characters
  INNER JOIN anime_characters
    ON anime_characters.character_id = characters.id
  INNER JOIN animes
    ON animes.id = anime_characters.anime_id
;
stdout2
SELECT
  characters.name,
  animes.title
FROM
  characters
  INNER JOIN anime_characters
    ON anime_characters.character_id = characters.id
  INNER JOIN animes
    ON animes.id = anime_characters.anime_id
WHERE
  animes.title = '偽物語'
;

ERB#result には Binding オブジェクトを渡す必要があります。通常 Kernel.#binding を渡しますが、できれば ERB テンプレート描画時に必要な値のみを渡したいです。

方法 2: Tilt を使う

$ gem install tilt
sample2.sql.erb
SELECT
  characters.name,
  animes.title
FROM
  characters
  INNER JOIN anime_characters
    ON anime_characters.character_id = characters.id
  INNER JOIN animes
    ON animes.id = anime_characters.anime_id
<% if locals.key?(:title) %>
WHERE
  animes.title = '<%= title %>'
<% end %>
;
require 'erb'
require 'tilt'

template2 = Tilt.new('sample2.sql.erb')

puts(template2.render(nil)) # 出力結果は stdout1 と同じ
puts(template2.render(nil, title: '偽物語')) # 出力結果は stdout2 と同じ

Tilt#render の第 2 引数に渡した hash は、ERB テンプレート中では locals という変数で参照できます。

参考

QUANON
あんた、マジなんだな?
http://quanon.github.io/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした