sqlalchemy，选择使用反向包含(不在列表中)的子列值列表 [英] sqlalchemy, select using reverse-inclusive (not in) list of child column values

问题描述

我在flask-sqlalchemy中具有典型的帖子/标签(与一个帖子相关联的许多标签)关系，并且我想在我提供的列表中选择未使用任何标签进行标签的帖子.首先，我建立的模型:

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

类似

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

失败

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

我认为这是因为标签是关系而不是列.当我手动编写实际的SQL时，我在另一个项目上进行了这项工作.这是有效的SQL:

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

如何使用sqlalchemy API实现此目标?

How would I achieve this using the sqlalchemy API?

推荐答案

使用否定的

Pretty straightforward using negated any:

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

这篇关于sqlalchemy，选择使用反向包含(不在列表中)的子列值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！