SQLAlchemy通过引用关系的混合属性进行排序 [英] SQLAlchemy order by hybrid property that references relationship

问题描述

我的SQLAlchemy模型:

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

因此，在Cover和Request之间建立了简单的一对多关系. number_of_requests混合属性应返回与该特定Cover关联的请求数.

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.

现在，在我的Flask路线之一中，我正在尝试按请求数获取前5个Covers.这就是现在的样子:

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

不幸的是，这给出了

ProgrammingError:(ProgrammingError)缺少表"request"的FROM子句条目

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

我怀疑这是因为在number_of_requests(cls)中我试图计算requests列表的大小，但是SQLAlchemy并未在原始查询中包括请求表.关于如何避免发生此错误的任何想法?

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?

推荐答案

将您的expression部分更改为:

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

并阅读 Correlated Subquery Relationship Hybrid .

这篇关于SQLAlchemy通过引用关系的混合属性进行排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！