SQLAlchemy ORM查询已联接到聚合子查询

Question

我有一个SQL查询：

SELECT d.device_name, d.device_category
FROM devices d
JOIN (
SELECT device_category, COUNT(*) AS cnt FROM devices GROUP BY device_category
) c ON c.device_category = d.device_category
ORDER BY c.cnt DESC

现在我需要用SQLAlchemyORM格式编写它(至少我愿意(。

我的尝试是：

sub_dquery = db.session.query(Devices.device_category, func.count('*')).group_by(Devices.device_category).subquery()
long_dquery = db.session.query(Devices.device_name, Devices.device_category).join(sub_dquery).desc()

我不知道如何实现这个部分：

c ON c.device_category = d.device_category

Answer 1

这看起来很接近：

with Session(engine) as session:
sub_dquery = (
session.query(Devices.device_category, func.count().label("cnt"))
.group_by(Devices.device_category)
.subquery("c")
)
long_dquery = (
session.query(Devices.device_name, Devices.device_category)
.join(sub_dquery, sub_dquery.c.device_category == Devices.device_category)
.order_by(sub_dquery.c.cnt.desc())
)
print(long_dquery)
"""
SELECT devices.device_name AS devices_device_name, devices.device_category AS devices_device_category 
FROM devices 
JOIN (
SELECT devices.device_category AS device_category, count(*) AS cnt 
FROM devices GROUP BY devices.device_category
) AS c ON c.device_category = devices.device_category 
ORDER BY c.cnt DESC
"""