0

I have the following sqlalchemy models:

class City(Base):
    __tablename__ = "cities"
    id = Column("id", Integer, primary_key=True)
    name = Column("name", String(50), nullable=False, unique=True)


class Pizzeria(Base):
    __tablename__ = "pizzerias"
    id = Column("id", Integer, primary_key=True)
    city_id = Column(
        "city_id", Integer, ForeignKey("cities.id"), nullable=False
    )
    name = Column("name", String(255), nullable=False)
    city = relationship(City)


class Restaurant(Base):
    __tablename__ = "restaurants"
    id = Column("id", Integer, primary_key=True)
    country_id = Column(
        "city_id", Integer, ForeignKey("cities.id"), nullable=False
    )
    name = Column("name", String(50), nullable=False)
    city = relationship(City)

Basically, I have a hierarchy: I have restaurants and pizzerias in a city. Each pizzeria is in a city. Each restaurant is in a city. Pizzerias and restaurants have no horizontal relationship.

I want to count the number of restaurants AND pizzerias in a list of cities, in one query.

If I want to count only the pizzerias in a list of cities, I can do that:

query = (
    session.query(
        City, func.count(Pizzeria.id)
    )
    .join(Pizzeria)
    .filter((City.name.in_(["Rome"])))
)

But I can't figure out how to count the number of pizzerias AND restaurants at once. I tried something like this:

query = (
    session.query(
        City, func.count(Pizzeria.id), func.count(Restaurant.id)
    )
    .join(Pizzeria)
    .join(Restaurant)
    .filter((City.name.in_(["Rome"])))
)

But I get the following error:

sqlalchemy.exc.InvalidRequestError: Can't determine which FROM clause to join from, there are multiple FROMS which can join to this entity. Try adding an explicit ON clause to help resolve the ambiguity.

Any idea?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.