问题描述
我正在努力使它与Postgres和Ecto一起使用.下面的查询在没有group_by的情况下可以正常工作,但我需要对似乎看不到的fragment字段进行分组.知道有什么问题吗?
I'm struggling to get this to work with Postgres and Ecto. The query below works fine without the group_by, but I need to group on the fragment field, which it can't seem to see. Any idea what's wrong with it?
def query_clicks do
from(Click)
|> select(
[c],
[
fragment("date_trunc('hour',?) as hour", c.inserted_at), c.link_id]
)
|> group_by([c], c.hour)
|> Repo.all
end
结果:
iex(1)> recompile; Shortr.LinkContext.query_clicks
[debug] QUERY ERROR source="clicks" db=1.2ms queue=4.9ms
SELECT date_trunc('hour',c0."inserted_at") as hour, c0."link_id" FROM "clicks" AS c0 GROUP BY c0."hour" []
** (Postgrex.Error) ERROR 42703 (undefined_column) column c0.hour does not exist
query: SELECT date_trunc('hour',c0."inserted_at") as hour, c0."link_id" FROM "clicks" AS c0 GROUP BY c0."hour"
(ecto_sql) lib/ecto/adapters/sql.ex:604: Ecto.Adapters.SQL.raise_sql_call_error/1
(ecto_sql) lib/ecto/adapters/sql.ex:537: Ecto.Adapters.SQL.execute/5
(ecto) lib/ecto/repo/queryable.ex:147: Ecto.Repo.Queryable.execute/4
(ecto) lib/ecto/repo/queryable.ex:18: Ecto.Repo.Queryable.all/3
iex(1)>
推荐答案
在生成的SQL中,您调用 c0.hour
,而将列别名为 hour
.应该是
In the resulting SQL you call c0.hour
whereas you alias the column to hour
. It should be
SELECT date_trunc('hour',c0."inserted_at") as hour, c0."link_id" FROM "clicks" AS c0 GROUP BY hour
在Ecto中,这将变成
In Ecto this would become
def query_clicks do
from(Click)
|> select(
[c],
[
fragment("date_trunc('hour',?) as hour", c.inserted_at), c.link_id
]
)
|> group_by([c], fragment("hour"))
|> Repo.all
end
另一个问题是 link_id
,SELECT子句中的任何字段都必须出现在GROUP BY子句中或为聚集体.例如,您还可以按 link_id
分组.
Another issue is the link_id
, any field in the SELECT clause must appear in the GROUP BY clause or be an aggregrate. You could for example also group by link_id
.
这篇关于使用片段&group_by和postgres&ecto?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!