SQLAlchemy ORM query joined to aggregate subquery

user_11902953 0
Viewed 86

I've got an SQL query:

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

And now I need to write it (at least, I would like to) in the SQLAlchemy ORM format.

My attempt is this:

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()

I don't know how to implement this part:

c ON c.device_category = d.device_category
sqlalchemy orm mysql sql
1 Answers

This looks pretty close:

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
    """
user_21443900
Related