Sqlalchemy hybrids

Question

I have a sqlalchemy (actually Flask-sqlalchemy hence all the db.*) and I would like to be able to sort my 'Things' by their average vote.value of the 'Votes' that they are related to. Votes have a value of 0 to 100.

Having run into the issue that sqlalchemy would like to translate the average_vote_value @attribute into sql and fails I find that I should probably be using hybrids:

However I just can't work out how that is done in this case. Can anyone help please?

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)

Answer 1

at the end of the day, you need to consider how to get the result you want as a SQL query. You can't think of it only in terms of "hybrid, python, properties", etc. While we're going to use those techniques to get the result, it's the way SQL works that leads us there. So let's use Postgresql and it's built in AVG function, which most databases have. We will need to JOIN from Thing to Vote, and since you want to consider the case where Thing has no Votes, a LEFT OUTER JOIN. The hybrid expression is just a syntactical helper for the SQL expression you want, but at the end of the day you still need to spell out the JOIN that SQL needs:

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', 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(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=7),
        Vote(value=8),
        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),
        Vote(value=10),
    ]),
    Thing(name="thing3", votes=[])
]
)
s.commit()

print s.query(Thing.name, Thing.average_vote_value).\
            outerjoin(Thing.votes).\
            group_by(Thing.name).all()

output (minus echo):

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