Sqlalchemy Hybrids

Question

I have sqlalchemy (actually Flask-sqlalchemy, therefore all db. *) And I would like to be able to sort my “Things” by their average number of votes. The meaning of the "Votes" with which they are associated. Votes range from 0 to 100.

Having run the problem in which sqlalchemy would like to convert average_vote_value @attribute to sql and failed, I found that I probably should use hybrids :

However, I just can’t understand how this is done in this case. Can anyone help?

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 think about how to get the desired result as an SQL query. You cannot think about it only in terms of "hybrid, python, properties", etc. Although we are going to use these methods to get the result, this is how SQL works, which leads us there. So let me use Postgresql, and it is built into the AVG function, which is available in most databases. We need to JOIN from Thing to Vote, and since you want to consider the case where Thing has no votes, LEFT OUTER JOIN. The hybrid expression is just the syntax helper for the SQL expression you need, but at the end of the day, you still need to specify the JOIN that SQL requires:

 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() 

(minus echo):

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