Sqlalchemy Many-to-many Relationship (tag System)

I have three tables: file, file_tag and tag. Now I want to find all files that have the tags with ID 1 and 3. class File_Tag(Base): __tablename__ = 'file_tag' id = Colu

Solution 1:

Please, try this:

SELECT file.id   AS file_id,
  file.name      AS file_name,
  file.sha1      AS file_sha1,
  file.import_id AS file_import_id
FROM file
INNER JOIN file_tag
ON file.id            = file_tag.file_id
WHERE file_tag.tag_id =1
AND file_tag.file_id IN
  (SELECT file_id FROM file_tag WHERE tag_id=3
  )
GROUP BY file.id;

Also, an sqlalchemy should be something like:

subq=session.query(File_Tag).filter(File_Tag.tag_id=3);
session.query(File).join(File.tags).filter((File_Tag.tag_id=1)&(File_Tag.file_id.in(subq)))

It contains a subquery, but I think it is not possible to achieve what you want without using them.