将带有子查询的SQLite查询转换为Peewee语句 [英] Translate SQLite query, with subquery, into Peewee statement
本文介绍了将带有子查询的SQLite查询转换为Peewee语句的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有一个满足我需要的SQL语句,但是我很难将其转换为相关的Peewee语句.这是我现在拥有的SQL,请注意,我现在正在使用子查询,但是我不在乎这是哪种子查询.
I've got a SQL statement that does what I need, but I'm having trouble converting it into the correlated Peewee statement. Here's the SQL I have now, note that I'm using a subquery right now, but I don't care that it's a subquery either way.
select t.name,
count(a.type_id) as total,
(
select count(id)
from assignment a
where a.course_id = 7
and a.due_date < date()
and a.type_id = t.id
group by a.type_id
order by a.type_id
) as completed
from assignment a
inner join type t on t.id = a.type_id
where a.course_id = 7
group by a.type_id
order by a.type_id
这是我最接近Peewee陈述的地方.目前,我只是在查询中使用静态数字作为别名,以便在模板中使用一个值,因此请忽略该部分.
Here's the closest I've come to the Peewee statement. Currently I'm aliasing a static number in the query just so that I have a value to work with in my template, so please ignore that part.
Assignment.select(
Type.name,
fn.Lower('1').alias('completed'),
fn.Count(Type.id).alias('total'),
).naive().join(Type).where(
Assignment.course==self,
).group_by(Type.id).order_by(Type.id)
推荐答案
您是否尝试过将子查询作为选择的一部分?
Have you tried just including the subquery as part of the select?
像这样吗?
query = (Assignment
.select(
Type.name,
fn.COUNT(Type.id).alias('total'),
Assignment.select(fn.COUNT(Assignment.id)).where(
(Assignment.due_date < fn.DATE()) &
(Assignment.course == 7) &
(Assignment.type == Type.id)
).group_by(Assignment.type).alias('completed'))
.join(Type)
.where(Assignment.course == 7)
.group_by(Type.name))
这篇关于将带有子查询的SQLite查询转换为Peewee语句的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文