SQLAlchemy order by hybrid property that refers to a relationship
My SQLAlchemy models:
class Cover(db.Model):
# ... a bunch of other fields ...
@hybrid_property
def number_of_requests(self):
if self.requests:
return len(self.requests)
return 0
@number_of_requests.expression
def number_of_requests(cls):
return func.count(cls.requests)
class Request(db.Model):
# ... a bunch of other fields ...
# Cover that this request is requesting
cover_id = db.Column(db.Integer, db.ForeignKey('cover.id')
cover = db.relationship('Cover',
backref=backref("requests", cascade="all, delete-orphan"))
So, a simple one-to-many relationship between Cover and Request. The flex property number_of_requests
should return the number of requests associated with this particular Skin.
Now, on one of my Flask routes, I'm trying to grab the top 5 Covers by Request count. This is what it looks like now:
# Get top cover requests
covers = Cover.query.order_by(Cover.number_of_requests).limit(5).all()
Unfortunately this gives
Programming ErrorError: (ProgrammingError) Missing FROM-clause entry for table query
I suspect this is because number_of_requests(cls)
I am trying to calculate the size of a list requests
, but SQLAlchemy did not include the query table in the original query. Any ideas on how to do this to avoid this error?
source to share
Change your part expression
to:
@number_of_requests.expression
def number_of_requests(cls):
return (select([func.count(Request.id)])
.where(Request.cover_id == cls.id))
and read again Correlated Subquery Relationship Hybrid
.
source to share