Ecto查询-日期+ Postgres间隔+查询插值 [英] Ecto Query - Dates + Postgres Intervals + Query Interpolation
问题描述
我想创建一个Ecto查询,以按年龄(即最小年龄(月)->最大年龄(月)过滤儿童
表中的记录。
I would like to create an Ecto query that filters records in a children
table by their age (i.e. "minimum age (months) -> maximum age (months)".
一种简单的方法是Ecto date_add
功能:
One simple way to do this would be the Ecto date_add
feature:
from c in Child, where: c.birthday >
datetime_add(^Ecto.DateTime.utc, -1, "month")
此问题是,并非所有孩子都在同一时区,而且肯定不是所有孩子都在 Etc / UTC
。该查询将非常接近,但没有发现(有些会在一天之内关闭)。
The issue with this is that not all children will be on the same time zone, and certainly not all on Etc/UTC
. This query would be pretty close, but not spot on (some would be off by a day).
我一直在尝试使用PostgreSQL的 interval
功能来使此查询正常工作,我可以使用SQL客户端使其正常工作,但是在遇到以下情况时会出现插值问题试图在片段中插入值。
I've been trying to use PostgreSQL's interval
functionality to make this query work. I can get it to work using an SQL client, but I'm getting interpolation issues when trying to interpolate values in a fragment.
这有效(孩子的时区来自其位置
关联) :
This works (a child's time zone is sourced from it's location
association):
query = from ch in Child,
join: loc in assoc(ch, :location),
where: ch.birthday <= fragment("(now() AT TIME ZONE ?)::date - interval '2 months'", loc.time_zone)
Repo.all(query)
请注意,我已经在'2个月'
间隔进行了硬编码。
Note that I've hard-coded in the '2 months'
interval.
我以为这样可以,但是不能:
I thought this would work, but does not:
query = from ch in Child,
join: loc in assoc(ch, :location),
where: ch.birthday <= fragment("(now() AT TIME ZONE ?)::date - interval ?", loc.time_zone, ^"2 months")
Repo.all(query)
请注意,我正在尝试使用Ecto的查询插值将'2 months'
的值引入查询中。
Note that I'm trying to use Ecto's query interpolation to bring in the '2 months'
value into the query.
错误如下:
[debug] QUERY ERROR source="children" db=1.7ms queue=0.1ms
SELECT c0."id", (... other properties) FROM "children" AS c0 INNER JOIN "programs" AS p2 ON p2."id" = c0."program_id" INNER JOIN "locations" AS l1 ON l1."id" = p2."location_id" WHERE (c0."birthday" <= (now() AT TIME ZONE l1."time_zone")::date - interval $1) ["2 months"]
** (Postgrex.Error) ERROR 42601 (syntax_error): syntax error at or near "$1"
(ecto) lib/ecto/adapters/sql.ex:436: Ecto.Adapters.SQL.execute_and_cache/7
(ecto) lib/ecto/repo/queryable.ex:130: Ecto.Repo.Queryable.execute/5
(ecto) lib/ecto/repo/queryable.ex:35: Ecto.Repo.Queryable.all/4
查询失败的部分(我在SQL客户端中尝试了相同的查询):
The part of the query that fails (I tried the same query in an SQL client) is:
(now()AT TIME ZONE。 time_zone):: date-interval $ 1)
它不喜欢 $ 1
部分。
我尝试在SQL客户端中使用单引号,但遇到相同的错误。我尝试了以下操作:
I tried using single-quotes in an SQL client, but got the same errors. I tried the following:
SELECT c0."id" FROM "children" AS c0 INNER JOIN "programs" AS p2 ON p2."id" = c0."program_id" INNER JOIN "locations" AS l1 ON l1."id" = p2."location_id" WHERE (c0."birthday" <= (now() AT TIME ZONE l1."time_zone")::date - interval $1) ['2 months']
任何帮助将不胜感激!
Any help would be appreciated!
推荐答案
我前一阵子需要做的很精确,最终使用了可以将区间与<$ c $相乘的事实c> $ 1 。
I needed to do exactly this a while ago and ended up using the fact that you can multiply intervals with $1
.
postgres=# select interval '1 year' - interval '1 month' * 5;
?column?
----------
7 mons
(1 row)
因此,这应该起作用:
query = from ch in Child,
join: loc in assoc(ch, :location),
where: ch.birthday <= fragment("(now() AT TIME ZONE ?)::date - interval '1 month' * ?", loc.time_zone, 2)
Repo.all(query)
这篇关于Ecto查询-日期+ Postgres间隔+查询插值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!