Elixir Ecto: 范围数据类型

从 PostgreSQL 9.2 以来, 可以用特定的操作符和函数存储和查询一个值的范围. 这是一个非常不错的特性, 比如在电子商务应用程序方面, 可以用一个字段 price_range 来替代 min_price, max_price 表示一个价格范围.

在Ecto中使用范围数据类型

价格范围的表示方法如下

[0, 45.67] 
[30.04, 98.50]
[100, 500]

我们可以用范围操作符来查询一个值的范围

2.5 <@ numrange(1.50,7)

Postgresql 支持如下范围类型

• int4range 整数方位

• int8range — 大整数范围

• numrange — 十进制数字范围(浮点)

• tsrange — 不带时区的时间戳范围

• tstzrange — 带时区的时间戳范围

• daterange — 日期范围

Ecto 本身为了兼容各个数据库, 并没有支持Postgresql 特有的Range数据类型, 但Ecto为我们提供了一个自定义数据类型的方法. 下面我们创建一个 NumRange 类型, 底层用了Postgrex适配器的%Postgrex.Range{}结构

defmodule App.NumRange do  
  @behaviour Ecto.Type
  def type, do: :numrange
  def cast([lower, upper]) do
    {:ok, [lower, upper]}
  end
  def cast(_), do: :error
  def load(%Postgrex.Range{lower: lower, upper: nil}) do
    {lower, _} = lower |> to_float
    {:ok, [lower, nil]}
  end
  def load(%Postgrex.Range{lower: lower, upper: upper}) do
    {lower, _} = lower |> to_float
    {upper, _} = upper |> to_float
    {:ok, [lower, upper]}
  end
  def dump([lower, upper]) do
    {:ok, %Postgrex.Range{lower: lower, upper: upper, upper_inclusive: false}}
  end
  def dump(_), do: :error
  defp to_float(value) do
   value |> Decimal.to_string |> Float.parse
  end
end 

在模型和迁移脚本中中使用这个自定义数据类型

模型

defmodule App.Product do  
  use App.Web, :model
  schema "products" do
    field :price_range, App.NumRange
    timestamps
  end
end  

迁移脚本

defmodule App.Repo.Migrations.Product do  
  use Ecto.Migration
  def change do
    create table(:products) do
      add :price_range, :numrange
      timestamps
    end
  end
end  

范围查询

defmodule App.ProductQuery do  
  import Ecto.Query
  alias App.{Repo, Product}
  def within_price_range(price) do
    query =
      from p in Product,
      where: fragment("?::numeric <@ ?", ^price, p.price_range)
    query |> Repo.all
  end
end  

关于方位边界的问题, 和数学中的范围符号是一样的:

• [ 包括下边界

• ( 不包括下边界

• ] 包括上边界

• ) 不包括上边界

例子

# 创建表
create table products(id int, price_range numrange);
# 插入数据
INSERT INTO products VALUES(1, '[10.0, 100.0]')
INSERT INTO products VALUES(1, '[200.0, 300.0]')
INSERT INTO products VALUES(1, '[400.0, 500.0]')
INSERT INTO products VALUES(1, '[600.0, 700.0]')
# 查询
SELECT * FROM products;
+------+--------------------------------------------------------+
|   id | price_range                                            |
|------+--------------------------------------------------------|
|    1 | NumericRange(Decimal('10.0'), Decimal('100.0'), '[]')  |
|    1 | NumericRange(Decimal('200.0'), Decimal('300.0'), '[]') |
|    1 | NumericRange(Decimal('400.0'), Decimal('500.0'), '[]') |
|    1 | NumericRange(Decimal('600.0'), Decimal('700.0'), '[]') |
+------+--------------------------------------------------------+
# 过滤
SELECT * FROM products WHERE price_range::numrange @> 99.0
+------+-------------------------------------------------------+
|   id | price_range                                           |
|------+-------------------------------------------------------|
|    1 | NumericRange(Decimal('10.0'), Decimal('100.0'), '[]') |
+------+-------------------------------------------------------+

参考资料

• 关于 Postgresql 的范围数据类型, 参考 范围类型

• Postgresql 范围操作符