読者です 読者をやめる 読者になる 読者になる

Sequel について (その2)

前回 からの続きです。

今回はいろんな 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に続く…(かも)