How to form complex mysql query that has left outer join, aggregate data with group by using SQLAlchemy?

Question

How to write the following query using SQLAlchemy?

SELECT i.itemid, sum(i.quantitysold) total_quantity_sold, max(t.createdAt) last_sale_time
FROM ItemList i LEFT OUTER JOIN ItemTransactions t ON i.itemid = t.itemid
WHERE i.active = 'y'
GROUP BY i.itemid
ORDER BY total_quantity_sold asc;

This is what I ended up writing:

from sqlalchemy.sql import func as sa_func

query = ItemList.query.with_entities(ItemList.itemid)
query = query.outerjoin(ItemTransactions, ItemTransactions.itemid == ItemList.itemid)
query = query.add_columns(sa_func.sum(ItemList.quantitySold).label('total_quantity_sold'))
query = query.add_columns(sa_func.max(ItemTransactions.createdAt).label('last_sale_time'))
query = query.filter(ItemList.active == "y")
query = query.group_by(ItemList.itemid)
query = query.order_by(sa_func.sum(ItemList.quantitySold).asc())

if limit is not None and limit is not 0:
    query = query.limit(limit)
if offset is not None:
    query = query.offset(offset)

# Execute the query
sales = query.all()

Answer 1

from sqlalchemy.sql import func

query = ItemList.query.with_entities(ItemList.itemid)
query = query.outerjoin(ItemTransactions, ItemTransactions.itemid == ItemList.itemid)
query = query.add_columns(func.sum(ItemList.quantitySold).label('total_quantity_sold'))
query = query.add_columns(func.max(ItemTransactions.createdAt).label('last_sale_time'))
query = query.filter(ItemList.active == "y")
query = query.group_by(ItemList.itemid)
query = query.order_by(func.sum(ItemList.quantitySold).asc())