How To Do A Join In Sqlalchemy On 3 Tables, Where One Of Them Is Mapping Between Other Two?

Suppose I have the following tables: Articles with fields article_id, title Tags with fields tag_id, name ArticleTags with fields article_id, tag_id And I wish to find all articl

Solution 1:

Assuming that you set the ForeignKey constraints correctly and created mappers:

q = Session.query(Articles).filter(Articles.article_id == ArticleTags.article_id).\
    filter(ArticleTags.tag_id == Tags.tag_id).\
    filter(Tags.name == 'tag_name')

If you have setup a Many-to-Many relation it's even more simple:

q = Session.query(Articles).filter(Articles.tags.any(name = 'tag_name'))

For some more examples for blog-related queries look here.

If you use the sql expression language, it should be straight forward translateable.