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.