在 SQLAlchemy 连接子句中使用函数输出 [英] Using function output in SQLAlchemy join clause
问题描述
我正在尝试将相当短的 SQL 转换为 sqlAlchemy ORM 查询.SQL 使用 Postgres 的 generate_series
制作一组日期,我的目标是制作一组按其中一个列分类的时间序列数组.
I am trying to translate a fairly short bit of SQL into an sqlAlchemy ORM query. The SQL uses Postgres's generate_series
to make a set of dates and my goal is to make a set of time series arrays categorized by one of the columns.
表格(简化版)非常简单:
The tables (simplified) are very simple:
counts:
-----------------
count (Integer)
day (Date)
placeID (foreign key related to places)
"counts_pkey" PRIMARY KEY (day, placeID)
places:
-----------------
id
name (varchar)
我所追求的输出是每个地方的计数时间序列,包括一天未报告计数时的空值.例如,这将对应于超过四天的系列:
The output I'm after is a time series of counts for each place including null values when counts are not reported for a day. For example, this would correspond to a series over four days:
array_agg | name
-----------------+-------------------
{NULL,0,7,NULL} | A Place
{NULL,1,NULL,2} | Some other place
{5,NULL,3,NULL} | Yet another
通过对日期范围和地点进行 CROSS JOIN
并将其与计数相结合,我可以很容易地做到这一点:
I can do this fairly easily by taking a CROSS JOIN
on a date range and places and joining that with the counts:
SELECT array_agg(counts.count), places.name
FROM generate_series('2018-11-01', '2018-11-04', interval '1 days') as day
CROSS JOIN places
LEFT OUTER JOIN counts on counts.day = day.day AND counts.PlaceID = places.id
GROUP BY places.name;
我似乎无法弄清楚如何让 SQLAlchemy 做到这一点.经过大量挖掘,我找到了一个旧的谷歌群组线程 几乎可以导致这个:
What I can't seem to figure out is how to get SQLAlchemy to do this. After a lot of digging, I found an old google groups thread which almost works leading to this:
date_list = select([column('generate_series')])\
.select_from(func.generate_series(backthen, today, '1 day'))\
.alias('date_list')
time_series = db.session.query(Place.name, func.array_agg(Count.count))\
.select_from(date_list)\
.outerjoin(Count, (Count.day == date_list.c.generate_series) & (Count.placeID == Place.id ))\
.group_by(Place.name)
这会为时间序列创建一个子选择,但会产生数据库错误:
This creates a sub-select for the time series, but it produces a database error:
表places"有一个条目,但不能从查询的这一部分引用.
There is an entry for table "places", but it cannot be referenced from this part of the query.
所以我的问题是:你会如何在 sqlalchemy 中做到这一点.另外,我认为这很困难,因为我使用 SQL 的方法是愚蠢的.
So my question is: how would you do this in sqlalchemy. Also, I'm open to the idea that this is difficult because my approach with the SQL is bone-headed.
推荐答案
问题是给定的查询构造 SQLAlchemy 生成了一个查询
The problem is that given the query construct SQLAlchemy produces a query along the lines of
SELECT ...
FROM places,
(...) AS date_list LEFT OUTER JOIN count ON ... AND count."placeID" = places.id
...
有 2 个 FROM
-list 项:places
和 join.项目不能相互交叉引用1,因此由于ON
子句中的places.id
而导致错误.
There are 2 FROM
-list items: places
and the join. Items cannot cross-reference each other1, and hence the error due to places.id
in the ON
-clause.
SQLAlchemy 不支持显式 CROSS JOIN
,但另一方面 CROSS JOIN
等效于 INNER JOIN ON (TRUE)
.您还可以省略在子查询中包装函数表达式,并按原样使用 给它一个别名:
SQLAlchemy does not support explicit CROSS JOIN
, but on the other hand a CROSS JOIN
is equivalent to an INNER JOIN ON (TRUE)
. You could also omit wrapping the function expression in a subquery and use it as is by giving it an alias:
date_list = func.generate_series(backthen, today, '1 day').alias('gen_day')
time_series = session.query(Place.name, func.array_agg(Count.count))\
.join(date_list, true())\
.outerjoin(Count, (Count.day == column('gen_day')) &
(Count.placeID == Place.id ))\
.group_by(Place.name)
1:除了函数调用FROM
-items,或者使用LATERAL
.
1: Except function-call FROM
-items, or using LATERAL
.
这篇关于在 SQLAlchemy 连接子句中使用函数输出的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!