sqlalchemy, select using reverse-inclusive (not in) list of child column values

Question

I have a typical Post / Tags (many tags associated with one post) relationship in flask-sqlalchemy, and I want to select posts which aren't tagged with any tag in a list I provide. First, the models I set up:

class Post(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    tags = db.relationship('Tag', lazy='dynamic')

class Tag(db.Model):
    id = db.Column(db.Integer, primary_key=True)
    name = db.Column(db.Text(50))
    post_id = db.Column(db.Integer, db.ForeignKey('post.id'))

Something like

db.session.query(Post).filter(Post.tags.name.notin_(['dont','want','these']))

fails with

AttributeError: Neither 'InstrumentedAttribute' object nor 'Comparator' object associated with Post.tags has an attribute 'name'

which I assume is because tags is a relationship and not a column. I had this working on another project when I was writing the actual SQL manually. This was the SQL that worked:

SELECT * FROM $posts WHERE id NOT IN (SELECT post_id FROM $tags WHERE name IN ('dont','want','these'))

How would I achieve this using the sqlalchemy API?

Answer 1

Pretty straightforward using negated any:

query = session.query(Post).filter(~Post.tags.any(Tag.name.in_(['dont', 'want', 'these'])))

Answer 2

Try this one, easy:

users = session.query(Post).filter(not_(Post.tags.name.in_(['dont', 'want', 'these'])))

Hope this helps!

Answer 3

The notin_ works for me, adjusted example:

db.session.query(Post).filter(Post.tags.notin_(['dont','want','these'])) 

Answer 4

I thought up a nasty solution, but it works for the time being. I'd be interested to hear if anyone comes up with a smarter method.

ignore_ids = [item.post_id for item in Tag.query.filter(Tag.name.in_(['dont','want','these'])).all()]
Post.query.filter(Post.id.notin_(ignore_ids))