SQLAlchemy order by hybrid property that references relationship

Question

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 number_of_requests hybrid property should return the number of Requests associated with that particular Cover.

Now, in one of my Flask routes, I'm trying to grab the top 5 Covers by number of Requests. Here's what that looks like now:

# Get top cover requests
covers = Cover.query.order_by(Cover.number_of_requests).limit(5).all()

Unfortunately, this gives

ProgrammingError: (ProgrammingError) missing FROM-clause entry for table "request"

I suspect this is because in number_of_requests(cls) I'm trying to count the size of the requests list but SQLAlchemy hasn't included the request table in the original query. Any ideas on how to do that to avoid getting this error?

score 5 · Accepted Answer · answered Sep 14 '14 at 20:13

5

Change your expression part to:

@number_of_requests.expression
def number_of_requests(cls):
    return (select([func.count(Request.id)])
            .where(Request.cover_id == cls.id))

and read Correlated Subquery Relationship Hybrid again.

answered Sep 14 '14 at 20:13

van

74,297
13
168
171

Out of curiosity, is there any way I can do that without having to import Request? – pgorsira Sep 14 '14 at 21:08
I guess you could do something like below, but basically you could use plain `sql` in this case. Ideally you need either a class `Request` or a table `request` thought. Should work for simple queries where you do not have aliases and multiple joins on the `Cover`: `(select([func.count('request.id')]).select_from('request').where("request.cover_id == cover.id"))` – van Sep 15 '14 at 07:03
Great, small change though: changed "request.cover‌_id == cover.id" to "request.cover‌_id = cover.id" and it works. – pgorsira Sep 20 '14 at 18:37
@van can one return list of related objects with eg limit in hybrid_propery /expression combo? – andilabs Aug 21 '19 at 17:50
@andilabs: I am not sure I understand the question in the context of this answer. Would you not just have use the `relationship` for related objects? maybe just a read-only with some special WHERE clause to select only top N related objects. – van Sep 11 '19 at 12:44
@van I need example for case like customer and having a property like last_10_purchases – andilabs Sep 11 '19 at 14:00
@van I wen for smth like this https://gist.github.com/andilabs/b4b715a1dd884110803cfc49929373bb – andilabs Sep 11 '19 at 14:07
1

@andilabs: please check this documentation page: [Row-Limited Relationships with Window Functions](https://docs.sqlalchemy.org/en/13/orm/join_conditions.html#row-limited-relationships-with-window-functions). You can use the example to construct a query for the `expression` part of your hybrid attribute. – van Sep 13 '19 at 06:05

SQLAlchemy order by hybrid property that references relationship

1 Answers1

Linked