SQLAlchemy how to filter by children in many to many

filtering, many-to-many, orm, python, sqlalchemy

Solution

query = (
    session.query(Post)
           .join(Post.tags)     # It's necessary to join the "children" of Post
           .filter(Post.date_out.between(start_date, end_date))
           # here comes the magic: 
           # you can filter with Tag, even though it was not directly joined)
           .filter(Tag.accepted == 1)
)

Disclaimer: this is a veeery reduced example of my actual code, I might have made a mistake while simplifying.

I hope it helps somebody.

Problem

I was asking for a problem I had in SQLAlchemy and found the solution while writing. I post it anyway just in case it helps somebody :) Let's say I have a many to many relationship that seems to work (at least I can fetch children) Three tables: posts, tags and post_tags. ``` import sqlalchemy as alc class Tag(Base): __tablename__ = 'tags' id = alc.Column(alc.Integer, primary_key=True) name = alc.Column(alc.String) accepted = alc.Column(alc.Integer) posts = relationship('Post', secondary=post_tags) class Post(Base): __tablename__ = 'posts' id = alc.Column(alc.Integer, primary_key=True) text = alc.Column(alc.String) date_out = alc.Column(alc.Date) tags = relationship('Mistake_Code', secondary=post_tags) # relational table post_tags = alc.Table('check_point_mistakes', Base.metadata, alc.Column('post_id', alc.Integer,ForeignKey('posts.id')), alc.Column('tag_id', alc.Integer, alc.ForeignKey('tags.id'))) ``` Now my problem is that I want to filter first by date_out in Post. I can get it like this: ``` # assume start_date and end_date query = ( session.query(Post) .filter(Post.date_out.between(start_date, end_date)) ) ``` But how to filter by tags at the same time?

Original source