使用sqlalchemy在一个查询中计算不同表中的条目数

Question

我有以下sqlalchemy模型：

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)

基本上，我有一个等级制度：我在一个城市里有餐馆和比萨店。每个比萨店都在一个城市里。每个餐厅都在一个城市里。比萨店和餐馆之间没有横向关系

我想在一个查询中计算一个城市列表中餐馆和比萨店的数量

如果我只想计算城市列表中的比萨店，我可以这样做：

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

但我不知道如何一下子数清比萨店和餐馆的数量。我试过这样的方法：

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

但我得到了以下错误：

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.

有什么想法吗