Flask-SQLAlchemy filter on many to many relationship with parent model

Question

I have a Parent model that a couple fo different types of items use as their parent via a foreign key. I also have a many to many relationship on the parent model. I am trying to get the child model based on querying the many to many model.

This is the parent model

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

And the many to many relationship

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

Finally there is an example fo the child model

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)

There are a couple of other types of child models as evidenced by the relationships defined in the MediaItem model.

I am looking to do filtering on the child model by the tag. That is to say that given a specific tag, return all the child models that are associated with that tag.

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

Returns that it doesnt know how to connect the three tables, (Could not find a FROM clause to join from. Tried joining to , but got: Can't find any foreign key relationships between 'media_item' and 'tags'.)

What could be my approach to this?

Answer 1

Version-1: The query below should return the desired result:

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

It might not be the most optimal, as it is produces SQL with two nested EXISTS clauses, but definitely it is very readable sqlalchemy query. Here is the query that is produced for 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
            )
        )
    )

Version-2: Somewhat more optimised query would be to join on the media_item, but then still perform exists on the tag:

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

which will produce the SQL as below:

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
    )

You could also join further on tags_joiner and tags and achieve the result. But this removes some flexibility: if you would like to perform and OR check on multiple tags, the result might return multiple Video rows, whereas keeping the query with EXISTS will take care of that.

---

Note, that your code has a media_tags, but it is not clear what it is.