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

how write following query using sqlalchemy?

select i.itemid, sum(i.quantitysold) total_quantity_sold, max(t.createdat) last_sale_time itemlist left outer join itemtransactions t on i.itemid = t.itemid i.active = 'y' group i.itemid order total_quantity_sold asc; 

this ended writing:

from sqlalchemy.sql import func 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 not none , limit not 0:     query = query.limit(limit) if offset not none:     query = query.offset(offset)  # execute query sales = query.all() 

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