昨日、PostgreSQL の範囲型についてという記事を投稿しました。
今回はこれを Rails で使ってみましょう。
そういう前提なので、この記事を読む前にPostgreSQL の範囲型についてに目を通しておくと分かりやすいと思います。
どのくらいのバージョンから使えるの？
Rails が PostgreSQL の範囲型に対応したのは 4.0.0 からのようです。
範囲型に対応した時のコミットのソースやテストを見ると、だいたいの使い方がわかります。
範囲型要素を持つ Model を作ってみよう
- PostgreSQL 側の準備
あ、PostgreSQL 9.2 以降がインストールされてる前提で進みますね
psql -c 'create user demo with createdb'
- Rails プロジェクト作成
gem i rails
rails new demo -d postgresql
cd demo
bundle
rake db:create
- Model の作成
rails g model daterange range:daterange
- インデックスの作成
rails g migration add_index_datarange_range
migration ファイルを下のように書き換える
20131219025930_add_index_datarange_range.rb
class AddIndexDatarangeRange < ActiveRecord::Migration
def change
add_index(:dateranges, :range, name: :by_dateranges_range, using: :gist)
end
end
マイグレーション実行
rake db:migrate
ちゃんとできたか、DB に訊いてみる
echo '\d dateranges' | rails db
結果
Table "public.dateranges"
Column | Type | Modifiers
------------+-----------------------------+---------------------------------------------------------
id | integer | not null default nextval('dateranges_id_seq'::regclass)
range | daterange |
created_at | timestamp without time zone |
updated_at | timestamp without time zone |
Indexes:
"dateranges_pkey" PRIMARY KEY, btree (id)
"by_dateranges_range" gist (range)
インデックスも出来てる
範囲型要素を持つ Model をいじってみよう
手始めに、Rails コンソール経由で１件ぶっこんでみましょう
create するときに Ruby の Range 型を渡してやれば、そのまま入ります。
Daterange.create(range: Date.parse("2013-01-01")..Date.parse("2013-12-31"))
結果
(0.1ms) BEGIN
SQL (2.3ms) INSERT INTO "dateranges" ("created_at", "range", "updated_at") VALUES ($1, $2, $3) RETURNING "id" [["created_at", Wed, 18 Dec 2013 13:46:37 UTC +00:00], ["range", Tue, 01 Jan 2013..Tue, 31 Dec 2013], ["updated_at", Wed, 18 Dec 2013 13:46:37 UTC +00:00]]
(0.8ms) COMMIT
#<Daterange id: 1, range: Tue, 01 Jan 2013..Tue, 31 Dec 2013, created_at: "2013-12-18 13:46:37", updated_at: "2013-12-18 13:46:37">
ちゃんと入った感じしますね。
今入れたレコードを DB のコンソールで確認してみましょう。
echo 'select id, range from dateranges order by id desc limit 1' | rails db
結果
id | range
----+-------------------------
1 | [2013-01-01,2014-01-01)
(1 row)
ちゃんと範囲型要素が収まっていますね。いいですね。
Ruby の範囲型
もう１件。
Daterange.create(range: Date.parse("2013-01-01")...Date.parse("2013-12-31"))
echo 'select id, range from dateranges order by id desc limit 1' | rails db
結果
id | range
----+-------------------------
2 | [2013-01-01,2013-12-31)
(1 row)
さて、１回目と２回目の違いはなんでしょうか。
並べて確認してみましょう。
Daterange.create(range: Date.parse("2013-01-01")..Date.parse("2013-12-31"))
Daterange.create(range: Date.parse("2013-01-01")...Date.parse("2013-12-31"))
そう。.. と ... の違いです。るりまの class Range によれば
範囲オブジェクトのクラス。範囲オブジェクトは範囲演算子 .. または ... によって生成されます。.. 演算子によって生成された範囲 オブジェクトは終端を含み、... 演算子によって生成された範囲オブジェ クトは終端を含みません。
とあります。.. は以上・以下、... は以上・未満なんです。みなさん知ってましたか？私は最近知りました。
だから最初のレコードでは [2013-01-01,2014-01-01) となっていて、次のレコードは [2013-01-01,2013-12-31) なんですね。range_to_string に５行くらいで分かりやすく実装されてます。
範囲型要素での絞り込み
絞り込みを試すため、DB にまとまったデータを投入しましょう。
適当にたくさんぶっ込む.rb
10_000.times do
# 適当な日
from = rand(Date.parse("1900-01-01")..Date.parse("2013-12-31"))
# from から 2013 年末までの適当な日
to = rand(from..Date.parse("2013-12-31"))
Daterange.create(range: from..to)
end
入ってるか確認
echo 'select count(*) from dateranges' | rails db
結果
count
-------
10002
(1 row)
キッチリ入っているようです。
まずは一番単純なクエリを試してみましょう
# 2013 年 1 月 1 日から 2013 年 12 月 31 日までの範囲と等しい範囲の Range オブジェクトを５件まで表示
year2013 = Date.parse("2013-01-01")..Date.parse("2013-12-31")
Daterange.where(range: year2013).limit(5).each do |dr|
puts dr.range
end
結果
Daterange Load (0.7ms) SELECT "dateranges".* FROM "dateranges" WHERE ("dateranges"."range" BETWEEN '2013-01-01' AND '2013-12-31') LIMIT 5
PG::InvalidTextRepresentation: ERROR: malformed range literal: "2013-01-01"
LINE 1: ..."dateranges" WHERE ("dateranges"."range" BETWEEN '2013-01-0...
^
そうなんです。ダメなんです。where に Range オブジェクトを渡すと、自動的に BETWEEN にしてくれるので、SQL 側で型が合わなくなってエラーになります。Range を BETWEEN に変換してくれる機能は普段は超便利。
でも今は邪魔だ...
じゃあ、こうかな？
# 2013 年 1 月 1 日から 2013 年 12 月 31 日までの範囲と等しい範囲の Range オブジェクトを５件まで表示
year2013 = Date.parse("2013-01-01")..Date.parse("2013-12-31")
Daterange.where("range = ?", year2013).limit(5).each do |dr|
puts dr.range
end
結果
Daterange Load (0.7ms) SELECT "dateranges".* FROM "dateranges" WHERE (range = '2013-01-01','2013-01-02','2013-01-03','2013-01-04','2013-01-05','2013-01-06','2013-01-07','2013-01-08','2013-01-09','2013-01-10','2013-01-11','2013-01-12','2013-01-13','2013-01-14','2013-01-15','2013-01-16','2013-01-17','2013-01-18','2013-01-19','2013-01-20','2013-01-21','2013-01-22','2013-01-23','2013-01-24','2013-01-25','2013-01-26','2013-01-27','2013-01-28','2013-01-29','2013-01-30','2013-01-31','2013-02-01','2013-02-02','2013-02-03','2013-02-04','2013-02-05','2013-02-06','2013-02-07','2013-02-08','2013-02-09','2013-02-10','2013-02-11','2013-02-12','2013-02-13','2013-02-14','2013-02-15','2013-02-16','2013-02-17','2013-02-18','2013-02-19','2013-02-20','2013-02-21','2013-02-22','2013-02-23','2013-02-24','2013-02-25','2013-02-26','2013-02-27','2013-02-28','2013-03-01','2013-03-02','2013-03-03','2013-03-04','2013-03-05','2013-03-06','2013-03-07','2013-03-08','2013-03-09','2013-03-10','2013-03-11','2013-03-12','2013-03-13','2013-03-14','2013-03-15','2013-03-16','2013-03-17','2013-03-18','2013-03-19','2013-03-20','2013-03-21','2013-03-22','2013-03-23','2013-03-24','2013-03-25','2013-03-26','2013-03-27','2013-03-28','2013-03-29','2013-03-30','2013-03-31','2013-04-01','2013-04-02','2013-04-03','2013-04-04','2013-04-05','2013-04-06','2013-04-07','2013-04-08','2013-04-09','2013-04-10','2013-04-11','2013-04-12','2013-04-13','2013-04-14','2013-04-15','2013-04-16','2013-04-17','2013-04-18','2013-04-19','2013-04-20','2013-04-21','2013-04-22','2013-04-23','2013-04-24','2013-04-25','2013-04-26','2013-04-27','2013-04-28','2013-04-29','2013-04-30','2013-05-01','2013-05-02','2013-05-03','2013-05-04','2013-05-05','2013-05-06','2013-05-07','2013-05-08','2013-05-09','2013-05-10','2013-05-11','2013-05-12','2013-05-13','2013-05-14','2013-05-15','2013-05-16','2013-05-17','2013-05-18','2013-05-19','2013-05-20','2013-05-21','2013-05-22','2013-05-23','2013-05-24','2013-05-25','2013-05-26','2013-05-27','2013-05-28','2013-05-29','2013-05-30','2013-05-31','2013-06-01','2013-06-02','2013-06-03','2013-06-04','2013-06-05','2013-06-06','2013-06-07','2013-06-08','2013-06-09','2013-06-10','2013-06-11','2013-06-12','2013-06-13','2013-06-14','2013-06-15','2013-06-16','2013-06-17','2013-06-18','2013-06-19','2013-06-20','2013-06-21','2013-06-22','2013-06-23','2013-06-24','2013-06-25','2013-06-26','2013-06-27','2013-06-28','2013-06-29','2013-06-30','2013-07-01','2013-07-02','2013-07-03','2013-07-04','2013-07-05','2013-07-06','2013-07-07','2013-07-08','2013-07-09','2013-07-10','2013-07-11','2013-07-12','2013-07-13','2013-07-14','2013-07-15','2013-07-16','2013-07-17','2013-07-18','2013-07-19','2013-07-20','2013-07-21','2013-07-22','2013-07-23','2013-07-24','2013-07-25','2013-07-26','2013-07-27','2013-07-28','2013-07-29','2013-07-30','2013-07-31','2013-08-01','2013-08-02','2013-08-03','2013-08-04','2013-08-05','2013-08-06','2013-08-07','2013-08-08','2013-08-09','2013-08-10','2013-08-11','2013-08-12','2013-08-13','2013-08-14','2013-08-15','2013-08-16','2013-08-17','2013-08-18','2013-08-19','2013-08-20','2013-08-21','2013-08-22','2013-08-23','2013-08-24','2013-08-25','2013-08-26','2013-08-27','2013-08-28','2013-08-29','2013-08-30','2013-08-31','2013-09-01','2013-09-02','2013-09-03','2013-09-04','2013-09-05','2013-09-06','2013-09-07','2013-09-08','2013-09-09','2013-09-10','2013-09-11','2013-09-12','2013-09-13','2013-09-14','2013-09-15','2013-09-16','2013-09-17','2013-09-18','2013-09-19','2013-09-20','2013-09-21','2013-09-22','2013-09-23','2013-09-24','2013-09-25','2013-09-26','2013-09-27','2013-09-28','2013-09-29','2013-09-30','2013-10-01','2013-10-02','2013-10-03','2013-10-04','2013-10-05','2013-10-06','2013-10-07','2013-10-08','2013-10-09','2013-10-10','2013-10-11','2013-10-12','2013-10-13','2013-10-14','2013-10-15','2013-10-16','2013-10-17','2013-10-18','2013-10-19','2013-10-20','2013-10-21','2013-10-22','2013-10-23','2013-10-24','2013-10-25','2013-10-26','2013-10-27','2013-10-28','2013-10-29','2013-10-30','2013-10-31','2013-11-01','2013-11-02','2013-11-03','2013-11-04','2013-11-05','2013-11-06','2013-11-07','2013-11-08','2013-11-09','2013-11-10','2013-11-11','2013-11-12','2013-11-13','2013-11-14','2013-11-15','2013-11-16','2013-11-17','2013-11-18','2013-11-19','2013-11-20','2013-11-21','2013-11-22','2013-11-23','2013-11-24','2013-11-25','2013-11-26','2013-11-27','2013-11-28','2013-11-29','2013-11-30','2013-12-01','2013-12-02','2013-12-03','2013-12-04','2013-12-05','2013-12-06','2013-12-07','2013-12-08','2013-12-09','2013-12-10','2013-12-11','2013-12-12','2013-12-13','2013-12-14','2013-12-15','2013-12-16','2013-12-17','2013-12-18','2013-12-19','2013-12-20','2013-12-21','2013-12-22','2013-12-23','2013-12-24','2013-12-25','2013-12-26','2013-12-27','2013-12-28','2013-12-29','2013-12-30','2013-12-31') LIMIT 5
PG::InvalidTextRepresentation: ERROR: malformed range literal: "2013-01-01"
LINE 1: ... "dateranges".* FROM "dateranges" WHERE (range = '2013-01-0...
てめぇｗ
こんな風に勝手に展開する機能があったとは... 便利そう...
でも今は邪魔だ...
どうしようか...
さっき見たrange_to_stringでも使うか？
include ActiveRecord::ConnectionAdapters::PostgreSQLColumn::Cast
# 2013 年 1 月 1 日から 2013 年 12 月 31 日までの範囲と等しい範囲の Range オブジェクトを５件まで表示
year2013 = Date.parse("2013-01-01")..Date.parse("2013-12-31")
Daterange.where("range = ?", range_to_string(year2013)).limit(5).each do |dr|
puts dr.range
end
結果
Daterange Load (2.6ms) SELECT "dateranges".* FROM "dateranges" WHERE (range = '[2013-01-01,2013-12-31]') LIMIT 5
2013-01-01...2014-01-01
いけたけど... いけたけど...
煮え切らない思いを抱えつつ、range_to_string を携えて次に行きます
便利な範囲演算子を使おう
範囲演算子は結構たくさんありますので、いくつか試してみましょう。
- 重複する(共通点を持つ)
# 2013年と一日でも重なりのある Range オブジェクトを５件まで表示
year2013 = Date.parse("2013-01-01")..Date.parse("2013-12-31")
Daterange.where("range && ?", range_to_string(year2013)).limit(5).each do |dr|
puts dr.range
end
結果
Daterange Load (0.5ms) SELECT "dateranges".* FROM "dateranges" WHERE (range && '[2013-01-01,2013-12-31]') LIMIT 5
2013-01-01...2014-01-01
2013-01-01...2013-12-31
2012-07-29...2013-08-01
1997-06-02...2013-02-12
2013-05-12...2013-05-18
- 要素を包含する
# 今日が含まれている範囲の Range オブジェクトを５件表示
# ?::date のキャストを忘れると動かないので注意
Daterange.where("range @> ?::date", Date.today).limit(5).each do |dr|
puts dr.range
end
結果
Daterange Load (0.9ms) SELECT "dateranges".* FROM "dateranges" WHERE (range @> '2013-12-19'::date) LIMIT 5
2013-01-01...2014-01-01
2013-01-01...2013-12-31
2013-12-09...2013-12-31
2013-12-13...2013-12-27
1970-11-29...2013-12-30
- 範囲を包含する
# 2012年の年末年始が丸々含まれている範囲の Range オブジェクトを５件まで表示
end_of_2012 = Date.parse("2012-12-29")..Date.parse("2013-01-03")
Daterange.where("range @> ?", range_to_string(end_of_2012)).limit(5).each do |dr|
puts dr.range
end
結果
Daterange Load (0.5ms) SELECT "dateranges".* FROM "dateranges" WHERE (range @> '[2012-12-29,2013-01-03]') LIMIT 5
2012-07-29...2013-08-01
1997-06-02...2013-02-12
2010-03-21...2013-09-08
2012-09-25...2013-05-03
2012-10-22...2013-12-19
- 隣接する
# うちの結婚記念日と隣接した範囲の Range オブジェクトを５件まで表示
anniversary = Date.parse("2008-08-29")..Date.parse("2008-08-29")
Daterange.where("range -|- ?", range_to_string(anniversary)).limit(5).each do |dr|
puts dr.range
end
結果
Daterange Load (4.0ms) SELECT "dateranges".* FROM "dateranges" WHERE (range -|- '[2008-08-29,2008-08-29]') LIMIT 5
2002-01-03...2008-08-29
まとめ
どうでしたか？夢ひろがりんぐ？
今回の例はかなり生で使ってる感がありましたが、それでも簡潔にやりたいことが表現できてると思います。
これが皆様のモデル設計のヒントになれば幸いです。
以上。長文にお付き合い頂き、ありがとうございました。