SQLAlachmey: ORM filter to match all items in a list, not any

Question

I want to search a SQLAlachmey list (via an association table) and match on multiple items within it via a filter.

I already reviewed this question but I am looking to accomplish this via the ORM filter only (and the second answer is not via an association table).

Database table setup:

tag_ast_table = Table('tag_association',
                  Base.metadata,
                  Column('file_id', Integer, ForeignKey('files.id')),
                  Column('tag_id', Integer, ForeignKey('tags.id')),
                  PrimaryKeyConstraint('file_id', 'tag_id'))


class File(Base):
    __tablename__ = 'files'

    id = Column(Integer, primary_key=True)
    tags = relationship("Tag", secondary=tag_ast_table)

class Tag(Base):
    __tablename__ = 'tags'

    id = Column(Integer, primary_key=True)
    tag = Column(String)

Current filter to match any I would like to modify to match all:

query = db.query(File).filter(File.tags.any(Tag.tag.in_(my_list))).all()

Answer 1

A reasonable approach to this in SQL (alluded to in your link) is to use having count(distinct tags.id) = <your number of tags>.

So the query needs 2 things: it needs an in that looks for your list of tags, and it needs a having that looks for the full count being present.

query = (
    session.query(File)
    .join(File.tags)
    .filter(Tag.tag.in_(search_tags))
    .group_by(File)
    .having(func.count(distinct(Tag.id)) == len(search_tags))
)

As an edge case, if search_tags is an empty list you won't get any results, so best to check for that first.