SQLAlchemy: filtering count in many-to-many relationship query

Question

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

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

It threw the following error:

AttributeError: Neither 'count' object nor 'Comparator' object has an attribute 'filter'

Here are some of the StackOverflow solutions I've tried to model (incl. solutions involving subqueries and hybrid properties):

I'm fairly new to SQL/SQLAlchemy... Any help is greatly appreciated!

Thank you for answering the question before I responded to your comment. I tried for a couple of hours to formulate the sql but I couldn't get it work... Thank you so much! — NTS, Apr 12 '15 at 21:26

Haleemur Ali · Accepted Answer · 2015-04-13T03:10:28.307

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.

filter is a method of query object, so you must write it as:

db.session.query(...).join(...).filter(...).group_by(...).order_by(...)

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

This will produce the following sql (using sqlite as the backend)

'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'

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.

If I run str(r) in my example query, it prints the sql quoted above.

SQLAlchemy: filtering count in many-to-many relationship query

1 Answers1

Linked