StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>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:</p> <pre class="lang-py prettyprint-override"><code>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() </code></pre> <p>output (minus echo):</p> <pre><code>[(u'thing3', 50.0), (u'thing1', 8.22222222222222), (u'thing2', 15.4)] </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload