Sqlalchemy杂交种

class Thing(db.Model):
    id = db.column(db.Integer,priMary_key=TruE)
    name = db.column(db.String(80))
    Votes = db.relationship('Vote',BACkref='thing',lazy='dynamic')

    @hybrid_property
    def average_Vote_value(self):
        '''average of Vote.values'''
        values = [v.value for v in self.Votes]
        try:
            return sum(scores) / len(values)
        except ZeroDivisionError:
            return 50 # the default value

    average_Vote_value.expression
    def average_Vote_value(cls):
        pass ### Help ###


class Vote(db.Model):
    id = db.column(db.Integer,priMary_key=TruE)
    thing_id = db.column(db.Integer,db.ForeignKey('thing.id'))
    value = db.column(db.Float,default=50.0)

解决方法@H_450_15@

在一天结束时,您需要考虑如何获得您想要的结果作为SQL查询.你不能仅仅从“混合,python,属性”等方面来考虑它.虽然我们将使用这些技术来获得结果,但这是sql工作方式引领我们的方式.所以让我们使用POSTGResql,它内置AVG功能,大多数数据库都有.我们将需要从Thing加入投票,因为你想要考虑Thing没有投票的情况,一个LEFT OUTER JOIN.混合表达式只是您想要的sql表达式的语法助手,但在一天结束时,您仍需要拼出sql所需的JOIN：

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.hybrid import hybrid_property
from sqlalchemy.ext.declarative import declarative_base

Base= declarative_base()

class Thing(BasE):
    __tablename__ = 'thing'
    id = column(Integer,priMary_key=TruE)
    name = column(String(80))
    Votes = relationship('Vote',lazy='dynamic')

    @hybrid_property
    def average_Vote_value(self):
        '''average of Vote.values'''
        values = [v.value for v in self.Votes]
        try:
            return sum(values) / len(values)
        except ZeroDivisionError:
            return 50 # the default value

    @average_Vote_value.expression
    def average_Vote_value(cls):
        return func.coalesce(func.avg(Vote.value),50)


class Vote(BasE):
    __tablename__ = 'Vote'
    id = column(Integer,priMary_key=TruE)
    thing_id = column(Integer,ForeignKey('thing.id'))
    value = column(Float,default=50.0)

e = create_ENGIne("POSTGResql://scott:tiger@localhost/test",echo=TruE)
Base.Metadata.drop_all(E)
Base.Metadata.create_all(E)

s = Session(E)

s.add_all([
    Thing(name="thing1",Votes=[
        Vote(value=5),Vote(value=7),Vote(value=8),Vote(value=12),Vote(value=2),Vote(value=15),Vote(value=10),]),Thing(name="thing2",Votes=[
        Vote(value=18),Vote(value=16),Vote(value=27),Vote(value=6),Thing(name="thing3",Votes=[])
]
)
s.commit()

print s.query(Thing.name,Thing.average_Vote_value).\
            outerjoin(Thing.Votes).\
            group_by(Thing.Name).all()

输出(减去回声)：

[(u'thing3',50.0),(u'thing1',8.22222222222222),(u'thing2',15.4)]