search
LoginSignup
17

More than 5 years have passed since last update.

posted at

updated at

Rails で PostgreSQL の範囲型を使ってみる

昨日、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 コンソール経由で1件ぶっこんでみましょう
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 の範囲型

もう1件。

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)

さて、1回目と2回目の違いはなんでしょうか。
並べて確認してみましょう。

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 に5行くらいで分かりやすく実装されてます。

範囲型要素での絞り込み

絞り込みを試すため、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 オブジェクトを5件まで表示
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 オブジェクトを5件まで表示
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...

てめぇw
こんな風に勝手に展開する機能があったとは... 便利そう...
でも今は邪魔だ...

どうしようか...
さっき見たrange_to_stringでも使うか?

include ActiveRecord::ConnectionAdapters::PostgreSQLColumn::Cast
# 2013 年 1 月 1 日から 2013 年 12 月 31 日までの範囲と等しい範囲の Range オブジェクトを5件まで表示
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 オブジェクトを5件まで表示
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 オブジェクトを5件表示
# ?::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 オブジェクトを5件まで表示
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 オブジェクトを5件まで表示
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

まとめ

どうでしたか?夢ひろがりんぐ?
今回の例はかなり生で使ってる感がありましたが、それでも簡潔にやりたいことが表現できてると思います。
これが皆様のモデル設計のヒントになれば幸いです。

以上。長文にお付き合い頂き、ありがとうございました。

おしらせ

昨日誕生日でした!ありがとうございます!

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
What you can do with signing up
17