前回 からの続きです。
今回はいろんな SELECT クエリを記述するために使用されるメソッド等について書いてみます。
データセット
その前にまずデータセットについて説明します。
テーブルからレコードやカラムを絞り込んだものをデータセットといいます。
DB[:tbl] はテーブル全体を表すデータセットオブジェクトです。
データセットオブジェクトに対する where, select 等のメソッドは元のデータセットを変更するのではなく、新たなデータセットを返します。
ds1 = DB[:tbl].where(:col1 => 123) #=> SELECT * FROM tbl WHERE col1 = 123 ds2 = ds1.where(:col2 => 456) #=> SELECT * FROM tbl WHERE col1 = 123 AND col2 = 456 ds3 = ds1.select(:col2) #=> SELECT col2 FROM tbl WHERE col1 = 123
データセットのメソッドを呼び出すことで、様々なクエリを表現することができます。
値の取り出し
データセットを作成しただけではクエリは実行されません。
実際にクエリを実行して値を取り出すには、each, all, get, first 等のメソッドを使います。
each
各レコードでブロックを繰り返します。
DB[:tbl].each{|row| ...} # (SELECT * FROM tbl)
row はカラム名がキーの Hash です。
all
全レコードを表す Hash の配列を返します。
DB[:tbl].all #=> [{:col1=>value, ...}, ...] (SELECT * FROM tbl)
get
最初のレコードの指定カラムの値を返します。 引数に配列を指定した場合は、値を配列で返します。
DB[:tbl].get(:col) #=> value (SELECT col FROM tbl LIMIT 1) DB[:tbl].get([:col1, :col2]) #=> [value1, value2] (SELECT col1, col2 FROM tbl LIMIT 1)
first
最初のレコードをいろんな形式で返します。
# 最初のレコードを表す Hash DB[:tbl].first #=> {:col=>value, ...} (SELECT * FROM tbl LIMIT 1) # 最初の n レコードを表す Hash の配列 DB[:tbl].first(n) #=> [{:col=>value, ...}, ...] (SELECT * FROM tbl LIMIT n) # 条件に一致する最初のレコードを表す Hash DB[:tbl].first(:col=>123) #=> {:col=>value, ...} (SELECT * FROM tbl WHERE col = 123 LIMIT 1)
select_map
カラムの値を返します。
# 全レコードの指定カラムの値の配列 DB[:tbl].select_map(:col) #=> [value, ...] (SELECT col FROM tbl) # 全レコードの指定カラムの値の配列の配列 DB[:tbl].select_map([:col1, :col2]) #=> [[value1, value2], ...] (SELECT col1, col2 FROM tbl)
to_hash
キーと値のカラム名を指定して Hash を得ます。
# id の値がキー、name の値が Hash の値 DB[:tbl].to_hash(:id, :col1) #=> {n => value1, ...} (SELECT * FROM tbl) # id の値がキー、レコードを表す Hash が値 DB[:tbl].to_hash(:id) #=> {n=>{:id=>n, :col1=>value1, ...}, ...} (SELECT * FROM tbl)
empty?
データセットが空かどうかを適切なクエリを発行して真偽値を返します。
DB[:tbl].empty? #=> true (SELECT 1 AS one FROM tbl1 LIMIT 1) DB[:tbl].where(:col1=>123).empty? #=> true (SELECT 1 AS one FROM tbl1 WHERE col1 = 123 LIMIT 1)
以降はデータセットに対するメソッドで、基本的にデータセットを返します。
ORDER
DB[:tbl].order(:col1) #=> SELECT * FROM tbl ORDER BY col1 DB[:tbl].order(Sequel.desc(:col1)) #=> SELECT * FROM tbl ORDER BY col1 DESC DB[:tbl].order(Sequel.expr(:col1).desc) #=> SELECT * FROM tbl ORDER BY col1 DESC DB[:tbl].order{col1.desc} #=> SELECT * FROM tbl ORDER BY col1 DESC
LIMIT
DB[:tbl].limit(10) #=> SELECT * FROM tbl LIMIT 10 DB[:tbl].limit(10, 30) #=> SELECT * FROM tbl LIMIT 10 OFFSET 30 DB[:tbl].limit(10..20) #=> SELECT * FROM tbl LIMIT 11 OFFSET 10
関数
DB[:tbl].where(Sequel.function(:hoge, :col1) > 10) #=> SELECT * FROM tbl WHERE hoge(col1) > 10 DB[:tbl].where{hoge(col1) > 10} #=> SELECT * FROM tbl WHERE hoge(col1) > 10 DB[:tbl].select(Sequel.function(:hoge, :col)) #=> SELECT hoge(col1) FROM tbl DB[:tbl].select{hoge(col1)} #=> SELECT hoge(col1) FROM tbl DB[:tbl].select(Sequel.function(:hoge) #=> SELECT hoge() FROM tbl DB[:tbl].select(Sequel.function(:hoge) #=> SELECT hoge() FROM tbl DB[:tbl].select{hoge{}} #=> SELECT hoge() FROM tbl
集約関数
DB[:tbl].count #=> n (SELECT count(*) AS count FROM tbl LIMIT 1) DB[:tbl].count(:col) #=> n (SELECT count(col) AS count FROM tbl LIMIT 1) DB[:tbl].sum(:col) #=> n (SELECT sum(col) AS sum FROM tbl LIMIT 1)
avg, count, max, min, sum が使用できます。
これらのメソッドはデータセットを返すのではなくて、その時点でクエリが実行され、結果を返します。
もしデータセットを返したい場合は通常の関数と同じように指定します。
DB[:tbl].select{count(:*){}.as(count)} #=> SELECT count(*) AS count FROM tbl
GROUP
DB[:tbl].group(:col1, :col2) #=> SELECT * FROM tbl GROUP BY col1, col2 DB[:tbl].group(:col1).having(:col1 => 123) #=> SELECT * FROM tbl GROUP BY col1 HAVING col1 = 123 DB[:tbl].select_group(:col) #=> SELECT col FROM tbl GROUP BY col DB[:tbl].group_and_count(:col) #=> SELECT col, count(*) AS count FROM tbl GROUP BY col DB[:tbl].select_group(:col).select_append{count(:*){}.as(count)} #=> SELECT col, count(*) AS count FROM tbl GROUP BY col
AS
カラム名のシンボルの代わりに、___ (アンダースコア3つ)をつなげて別名を指定することができます。
DB[:tbl].select(:col___hoge) #=> SELECT col AS hoge FROM Tbl
Sequel.expr 等で作成された Sequel のオブジェクトに対しては as メソッドも使用できます。
DB[:tbl].select(Sequel.expr(:col).as(:hoge)) #=> SELECT col AS hoge FROM Tbl
JOIN
第1引数に結合したいテーブル名、第2引数に結合条件を Hash で指定します。
DB[:tbl1].join(:tbl2) #=> SELECT * FROM tbl1 INNER JOIN tbl2 DB[:tbl1].left_join(:tbl2, :col1 => :col2) #=> SELECT * FROM tbl1 LEFT JOIN tbl2 ON tbl2.col1 = tbl1.col2
結合条件の Hash のキーや値にシンボルを指定した場合、キーは第1引数のテーブルのカラムとして、値は元のテーブルのカラムとして使用されます。
inner_join, full_outer_join, right_join, left_outer_join, full_join, right_join, left_join, natural_join, natural_left_join, natural_right_join, natural_full_join, cross_join が使用できます。
テーブルもカラムと同様 ___ で別名を指定できます。
DB[:tbl1___hoge].join(:tbl2___fuga) #=> SELECT * FROM tbl1 INNER JOIN tbl2
サブクエリ
条件を表す Hash の値にデータセットを指定すると、サブクエリを生成します。
DB[:tbl1].where(:id => db[:tbl2].where(:col1=>123).select(:col2)).sql #=> SELECT * FROM tbl1 WHERE id IN (SELECT col2 FROM tbl2 WHERE col1 = 123)
FOR UPDATE
DB[:tbl].where(:col => 123).for_update #=> SELECT * FROM tbl WHERE col = 123 FOR UPDATE
続く
その3に続く…(かも)
