python - sqlalchemy 同时从 2 个表中计数

Question

我有一个模型案例，可以这样说:

class Case(Base):
    __tablename__ = "cases"
    id = db.Column("id", db.Integer, primary_key=True)

啤酒模型，如下所示:

class Beer(Base):
    __tablename__ = "beers"
    id = db.Column("id", db.Integer, primary_key=True)
    case_id = db.Column(
        "case_id", db.Integer, db.ForeignKey("cases.id"), nullable=False, index=True
    )

还有苏打水模型，如下所示:

class Soda(Base):
    __tablename__ = "sodas"
    id = db.Column("id", db.Integer, primary_key=True)
    case_id = db.Column(
        "case_id", db.Integer, db.ForeignKey("cases.id"), nullable=False, index=True
    )

苏打水或啤酒对象必然属于一个案例。因此，箱子的等级高于苏打水或啤酒。苏打水和啤酒无论如何都没有关系。苏打水和啤酒可以属于同一个箱子。

对于每个案例，我想获取啤酒和可乐的数量。

我尝试过这样的事情:

all_nbr = session.query(Case, func.count(Beer.id), func.count(Coke.id)).join(Beer, Coke).group_by(Case.id)

但它不起作用(我相信它只给我啤酒的数量)。

你有什么想法吗？

Answer 1

可以使用子查询来解决这个问题

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
from decimal import Decimal
from sqlalchemy import func

engine = sa.create_engine('sqlite:///:memory:')
Base = declarative_base()
session = sa.orm.sessionmaker(bind=engine)()

class Case(Base):
    __tablename__ = "cases"
    id = sa.Column("id", sa.Integer, primary_key=True)


class Beer(Base):
    __tablename__ = "beers"
    id = sa.Column("id", sa.Integer, primary_key=True)
    case_id = sa.Column(
        "case_id", sa.Integer, sa.ForeignKey("cases.id"), nullable=False, index=True
    )


class Soda(Base):
    __tablename__ = "sodas"
    id = sa.Column("id", sa.Integer, primary_key=True)
    case_id = sa.Column(
        "case_id", sa.Integer, sa.ForeignKey("cases.id"), nullable=False, index=True
    )

Base.metadata.create_all(engine)

engine.echo = True

session.add(Case(id=1))
session.add(Case(id=2))

session.add(Beer(id=1, case_id=1))
session.add(Beer(id=2, case_id=1))
session.add(Beer(id=3, case_id=1))
session.add(Beer(id=4, case_id=2))


session.add(Soda(id=1, case_id=2))
session.add(Soda(id=2, case_id=2))
session.add(Soda(id=3, case_id=2))
session.add(Soda(id=4, case_id=1))

session.commit()

beer_count = (session.query(
                Case.id, 
                func.count(Beer.id).label("total_beers")
            ).
            outerjoin(Beer, Beer.case_id == Case.id).
            group_by(Beer.case_id)
            ).subquery()

soda_count = (session.query(
                Case.id, 
                func.count(Soda.id).label("total_sodas")
            ).
            outerjoin(Soda, Soda.case_id == Case.id).
            group_by(Soda.case_id)
            ).subquery()

cases = (session.query(
            Case, 
            beer_count.c.total_beers,
            soda_count.c.total_sodas,
        ).
        outerjoin(beer_count, beer_count.c.id == Case.id)
        .outerjoin(soda_count, soda_count.c.id == Case.id)
        .group_by(Case)
        )

这将为您提供每箱啤酒和苏打水的数量