联接sqlalchemy语句中筛选器的子查询

Question

在sqlalchemy中，我在两个表之间有一对多的映射：一个表表示运动员，另一个表对应运动员的成绩。运动员可以有任意数量的分数。我试图根据运动员成绩的乘积来筛选运动员。以下是两个表的代码：

ECHO = False
from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer
from sqlalchemy import ForeignKey
from sqlalchemy.orm import relationship, backref


engine = create_engine('sqlite:///:memory:', echo=ECHO)
Base = declarative_base()
class Athlete(Base):
   __tablename__ = 'athletes'

   id = Column(Integer, primary_key=True)

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)
session = Session()

athlete0 = Athlete(id = 0)
athlete1 = Athlete(id = 1)
athlete2 = Athlete(id = 2)

session.add_all([
     athlete0,
     athlete1,
     athlete2])

session.commit()

class Score(Base):
    __tablename__ = 'scores'
    pos = Column(Integer, primary_key=True)
    score = Column(Integer)
    athlete_id = Column(Integer, ForeignKey('athletes.id'))
    athlete = relationship("Athlete", backref=backref('scores', order_by=pos))

Base.metadata.create_all(engine)

athlete0.scores = [Score(score = 4), Score(score = 3), Score(score = 5)]
athlete1.scores = [Score(score = 2), Score(score = 1)]
athlete2.scores = [Score(score = 3), Score(score = 8), Score(score = 10), Score(score = 7)]
session.commit()

下面是我想做的事情：

^{pr2}$

Answer 1

希望这段代码能对你有所帮助。 只需在运动员课程中增加混合动力车属性。在

from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.sql.expression import select, func

class Athlete(Base):
    __tablename__ = 'athletes'

    id = Column(Integer, primary_key=True)

    @hybrid_property
    def product_of_score(self):
        return sum(r.score for r in self.scores)

    @product_of_score.expression
    def product_of_score(self):
        return select([func.sum(Score.score)]).\
            where(Score.athlete_id==self.id).\
            label('product_of_score')

问题是：

^{pr2}$