python - Flask-SQLAlchemy 过滤与父模型的多对多关系

Question

我有一个父模型，几个不同类型的项目通过外键用作它们的父模型。我在父模型上也有多对多关系。我正在尝试基于查询多对多模型来获取子模型。

这是父模型

class MediaItem(db.Model):
    __tablename__ = "media_item"
    id = db.Column(db.Integer, primary_key=True)
    title = db.Column(db.String, unique=True)
    tags = db.relationship('Tags', secondary=tags_joiner, backref='media_items')
    videos = db.relationship('Video', backref='Parent', lazy='dynamic')
    audios = db.relationship('Audio', backref='Parent', lazy='dynamic')
    pictures = db.relationship('Picture', backref='Parent', lazy='dynamic')
    codes = db.relationship('Code', backref='Parent', lazy='dynamic')

多对多关系

class Tags(db.Model):
    __tablename__ = 'tags'
    id = db.Column(db.Integer, primary_key=True)
    tag = db.Column(db.String, unique=True, nullable=False)


tags_joiner = db.Table('tags_joiner',
                       db.Column('tag_id', db.Integer, db.ForeignKey('tags.id')),
                       db.Column('mediaitem_id', db.Integer, db.ForeignKey('media_item.id')),
                       db.PrimaryKeyConstraint('tag_id', 'mediaitem_id'))

终于有子模型的例子了

class Video(db.Model):
    __tablename__ = 'video'
    id = db.Column(db.Integer, primary_key=True)
    parent_id = db.Column(db.Integer, db.ForeignKey('media_item.id'))
    file_name = db.Column(db.String, unique=True)

MediaItem 模型中定义的关系证明了还有一些其他类型的子模型。

我希望通过标签对子模型进行过滤。也就是说，给定一个特定的标签，返回与该标签关联的所有子模型。

Video.query.join(media_tags).filter_by(MediaItem.tags.any(Tags.tag.in_(tag)))

返回它不知道如何连接这三个表，(无法找到要连接的 FROM 子句。尝试连接到 ，但得到:找不到“media_item”和“tags”之间的任何外键关系。 )

我的方法是什么？

Answer 1

版本 1:下面的查询应该返回所需的结果:

tag = 'my_filter_tag'
q = (
    db.session
    .query(Video)
    .filter(Video.Parent.has(MediaItem.tags.any(Tags.tag == tag)))
)

它可能不是最优的，因为它生成带有两个嵌套的 EXISTS 子句的 SQL，但它绝对是非常可读的 sqlalchemy询问。这是为 sqlite 生成的查询:

SELECT  video.id AS video_id, video.parent_id AS video_parent_id, video.file_name AS video_file_name
FROM    video
WHERE EXISTS (
    SELECT  1
    FROM    media_item
    WHERE   media_item.id = video.parent_id
        AND (
        EXISTS (
            SELECT 1
            FROM    tags_joiner, tags
            WHERE   media_item.id = tags_joiner.mediaitem_id
                AND tags.id = tags_joiner.tag_id
                AND tags.tag = :tag_1
            )
        )
    )

版本 2:一些更优化的查询将加入 media_item，但仍然对标签执行 exists:

q = (
    db.session
    .query(Video)
    .join(MediaItem, Video.Parent)
    .filter(MediaItem.tags.any(Tags.tag == tag))
)

这将生成如下 SQL:

SELECT  video.id AS video_id, video.parent_id AS video_parent_id, video.file_name AS video_file_name
FROM    video
JOIN    media_item
    ON  media_item.id = video.parent_id
WHERE   EXISTS (
    SELECT  1
    FROM    tags_joiner, tags
    WHERE   media_item.id = tags_joiner.mediaitem_id
        AND tags.id = tags_joiner.tag_id
        AND tags.tag = :tag_1
    )

您还可以进一步加入 tags_joiner 和 tags 并获得结果。但这消除了一些灵 active :如果您想对多个标签执行 OR 检查，结果可能会返回多个 Video 行，同时使用 EXISTS 保持查询 会处理这个问题。

---

请注意，您的代码有一个 media_tags，但不清楚它是什么。