问题描述
在我的Flask应用中,文档和令牌之间存在多对多关系:
In my Flask app, there is a many-to-many relationship between Documents and Tokens:
DocTokens = db.Table(
'DocTokens',
db.Column('token_id', db.Integer, db.ForeignKey('Token.id')),
db.Column('document_id', db.Integer, db.ForeignKey('Document.id')),
)
class Token(db.Model):
__tablename__ = 'Token'
id = db.Column(db.Integer, primary_key=True)
...
is_gold = db.Column(db.Boolean, default=None)
class Document(db.Model):
__tablename__ = 'Document'
id = db.Column(db.Integer, primary_key=True)
...
tokens = db.relationship(
'Token',
secondary=DocTokens,
backred=db.backref('documents', lazy='dynamic'),
)
我想构造一个文档查询,按相关令牌的数量.
I'd like to construct a Document query, ordered (descendingly) by the number of related Tokens where Token.is_gold is None.
到目前为止,我已经根据相关令牌的数量确定了如何订购文档:
So far, I've figured out how to order Documents by the number of related Tokens:
db.session.query(
Document,
func.count(DocTokens.c.token_id).label('total')
).join(DocTokens).group_by(Document).order_by('total DESC')
但是,我似乎无法使该计数仅包含Token.is_gold为None的令牌.这是许多失败的尝试之一:
But, I can't seem to make that count include only Tokens where Token.is_gold is None. Here is one of many failed attempts:
db.session.query(
Document,
func.count(DocTokens.c.token_id)
.filter(Token.is_gold.is_(None)).label('total')
).join(DocTokens).group_by(Document).order_by('total DESC')
它引发了以下错误:
AttributeError: Neither 'count' object nor 'Comparator' object has an attribute 'filter'
以下是我尝试建模的一些StackOverflow解决方案(包括涉及子查询和混合属性的解决方案):
Here are some of the StackOverflow solutions I've tried to model (incl. solutions involving subqueries and hybrid properties):
我对SQL/SQLAlchemy还是很陌生...非常感谢您的帮助!
I'm fairly new to SQL/SQLAlchemy... Any help is greatly appreciated!
推荐答案
-
标签
应该应用于func.count(DocTokens.c.token_id)
,而不是过滤器对象.您在第一个查询中就正确了,但在第二个查询中却没有.The
label
should be applied tofunc.count(DocTokens.c.token_id)
, and not the filter object. You had it right in your first query, but not in the second.过滤器
是query
对象的一种方法,因此您必须将其编写为:filter
is a method ofquery
object, so you must write it as:db.session.query(...).join(...).filter(...).group_by(...).order_by(...)
-
过滤器正在应用
Token
中的列,因此必须将其包括在联接中. the filter is applying on a column from
Token
, so this must be included in the join.因此,按以下格式编写的查询不会给您错误:
Thus, the query written as the following will not give you an error:
r = db.session.query(Document, func.count(Token.id).label('total'))\ .join(DocTokens).join(Token)\ .filter(Token.is_gold.is_(None))\ .group_by(Document)\ .order_by('total DESC')
这将产生以下sql(使用sqlite作为后端)
This will produce the following sql (using sqlite as the backend)
更新:如果不确定要从查询对象生成什么sql,则始终可以使用
str
(即update: if you're not sure what sql will be generated from a query object, you can always examine it with a
str
, i.e.如果我在示例查询中运行
str(r)
,它将打印上面引用的sql.If I run
str(r)
in my example query, it prints the sql quoted above.这篇关于SQLAlchemy:多对多关系查询中的过滤计数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!