Search code examples
pythonjoinsqlalchemydistinctfastapi

How to query multiple tables using join in sqlalchemy

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

Solution

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