Subquery join with Sum and Order By With SQLAlchemy

Viewed 17

I am trying to write a query in SQLAlchemy that leverages a subquery to get sums of columns to order the results of the primary table by. In SQL I'm able to do this with:

SELECT il.item
FROM (
    SELECT item, SUM(item_count) as item_count
    FROM item_inventory
    GROUP BY item) ii
JOIN item_list il ON il.item = ii.item
ORDER BY ii.item_count DESC;

What would be the best way to go about this with SQLAlchemy? I've tried the below which works:

subquery = select(ItemInventory.item
        , func.sum(ItemInventory.item_count).label("item_sum"))
        .group_by(ItemInventory.item).subquery()

main_query = select(ItemList.item)\
    .join(subquery, subquery.c.item == ItemList.item)\
    .order_by(subquery.c.item_sum.desc())

EDIT: I had originally made a mistype in the above which was causing it to not work.

I know I'm likely doing something wrong (UPDATE: I was it was a mistype) and trying to work through that, but figured I'd also ask in case there's also a better way.

0 Answers
Related