How to query multiple tables using join in sqlalchemy-CodePudding

select count(DISTINCT(a.cust_id)) as count ,b.code, b.name from table1 as a inner join table2 as b on a.par_id = b.id where a.data = "present" group by a.par_id order by b.name asc;

How to write this in sqlalchemy to get as expected results

The above query which is writen in sql should be right in sqlalchemy.

Thanks for inputs

CodePudding user response：

Hope this works...

session.query(
    func.count(distinct(table1.cust_id)).label('count'),
    table2.code,
    table2.name
).join(
    table2,
    table1.par_id == table2.id
).filter(
    table1.data == "present"
).group_by(
    table1.par_id
).order_by(
    table2.name.asc()
).all()