SqlAlchemy Left Join with count

Question

class Employee
    id
    name

class Location
    id
    city

class Work
    id
    employee_id
    location_id

Todo: Left join with count (including zero)

Desired result

    location.city count
    NYC 10
    SFO 5
    CHI 0

Raw Query:

    select location.id, count(work.id) as count
    from location 
    left join work
    on location.id = work.location_id
    group by location.id

SqlAlchemy:

    db_session.query(Location, func.count.work_id).label('count')). \
                filter(location.id == work.location_id). \
                group_by(location._id). \

What is the right way to specify columns in select while doing a left join?

Answer 1

Define a relationship between your tables when you define the orm model. See this tutorial. In your example, Work is a association table, so if you don't have any other data in Work, you can use a many to many relationship e.g.:

from sqlalchemy.orm import relationship

class Work
    id
    employee_id
    location_id

class Employee
    id
    name

class Location
    id
    city
    employees = relationship(Employee, secondary=Work, backref='locations')

Then query:

session.query(Location, func.count(Work.id)).outerjoin(Work).group_by(Location)

As you have defined the relationship, sqlalchemy will know the direction, and columns for the outerjoin.

When you do the group_by, it is important to pass Location and not just Location.id, because by doing select(Location), sqlalchemy will select all columns in Location, and so you must also pass all columns in Location.