SQLAlchemy:多对多关系查询中的过滤计数 [英] SQLAlchemy: filtering count in many-to-many relationship query

问题描述

在我的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'),
        )

我想构造一个文档查询，按相关令牌的数量<降序排列(降序)，其中Token.is_gold为None .

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

• 如何按计数排序SQLAlchemy中的多对多关系?
• 按多对多关系排序-SQLAlchemy
• SQLAlchemy的排序方式关系
• 通过包含在SQLAlchemy

我对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 to func.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 of query 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)

    'SELECT"Document" .id AS"Document_id"，count("DocTokens" .token_id)AS总计\ nFROM"Token"，"Document" JOIN"DocTokens" ON"Document" .id ="DocTokens" .document_id\ nWHERE令牌" .is_gold是文档" .id的空分组.id按总DESC的顺序排列

    'SELECT "Document".id AS "Document_id", count("DocTokens".token_id) AS total \nFROM "Token", "Document" JOIN "DocTokens" ON "Document".id = "DocTokens".document_id \nWHERE "Token".is_gold IS NULL GROUP BY "Document".id ORDER BY total DESC'

    更新:如果不确定要从查询对象生成什么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:多对多关系查询中的过滤计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！