I have the follwing SQL query (It get's the largest of a certain column per group, with 3 things to group by):
我有以下SQL查询(它是每个组中某个列中最大的一个,有3个要分组的东西):
select p1.Name, p1.nvr, p1.Arch, d1.repo, p1.Date
from Packages as p1 inner join
Distribution as d1
on p1.rpm_id = d1.rpm_id inner join (
select Name, Arch, repo, max(Date) as Date
from Packages inner join Distribution
on Packages.rpm_id = Distribution.rpm_id
where Name like 'p%' and repo not like '%staging'
group by Name, Arch, repo
) as sq
on p1.Name = sq.Name and p1.Arch = sq.Arch and d1.repo = sq.repo and p1.Date = sq.Date
order by p1.nvr
And I'm trying to convert it to SQLAlchemy. This is what I have so far:
我正在尝试将其转换为SQLAlchemy。这是我到目前为止:
p1 = aliased(Packages)
d1 = aliased(Distribution)
sq = session.\
query(
Packages.Name,
Packages.Arch,
Distribution.repo,
func.max(Packages.Date).\
label('Date')).\
select_from(
Packages).\
join(
Distribution).\
filter(
queryfilter).\
filter(
not_(Distribution.repo.\
like('%staging'))).\
group_by(
Packages.Name,
Packages.Arch,
Distribution.repo).subquery()
result = session.\
query(
p1, d1.repo).\
select_from(
p1).\
join(
d1).\
join(
sq,
p1.Name==sq.c.Name,
p1.Arch==sq.c.Arch,
d1.repo==sq.c.repo,
p1.Date==sq.c.Date).\
order_by(p1.nvr).all()
The problem arises when I do the join on the subquery. I get an error that states that there is no from clause to join from. This is strange because I specify one right after the subquery in the join funciton as an argument. Any idea what I'm doing wrong? Perhaps I need to alias something and do a select_from again?
当我在子查询上进行连接时出现问题。我收到一条错误,指出没有from子句可以加入。这很奇怪,因为我在join函数中的子查询后面指定一个作为参数。知道我做错了什么吗?也许我需要别名并再次执行select_from?
EDIT: Exact error
编辑:确切的错误
Could not find a FROM clause to join from. Tried joining to SELECT "Packages"."Name", "Packages"."Arch", "Distribution".repo, max("Packages"."Date") AS "Date" FROM "Packages" JOIN "Distribution" ON "Packages".rpm_id = "Distribution".rpm_id WHERE "Packages"."Name" LIKE :Name_1 AND "Distribution".repo NOT LIKE :repo_1 GROUP BY "Packages"."Name", "Packages"."Arch", "Distribution".repo, but got: Can't find any foreign key relationships between 'Join object on %(139953254400272 Packages)s(139953254400272) and %(139953256322768 Distribution)s(139953256322768)' and '%(139953257005520 anon)s'.
It's trying to join, but it says it doesn't know where to make the join. Is there something wrong with my syntax? I think it's correct based on what's in the join function.
它试图加入,但它说它不知道在哪里进行加入。我的语法有问题吗?我认为根据连接函数中的内容是正确的。
1 个解决方案
#1
11
Apparently you need to add an and_()
around multiple join conditions.
显然你需要在多个连接条件周围添加一个and_()。
join(
sq,
and_(p1.Name==sq.c.Name,
p1.Arch==sq.c.Arch,
d1.repo==sq.c.repo,
p1.Date==sq.c.Date)).\
#1
11
Apparently you need to add an and_()
around multiple join conditions.
显然你需要在多个连接条件周围添加一个and_()。
join(
sq,
and_(p1.Name==sq.c.Name,
p1.Arch==sq.c.Arch,
d1.repo==sq.c.repo,
p1.Date==sq.c.Date)).\