How to do a JOIN in SQLAlchemy on 3 tables, where one of them is mapping between other two?

Question

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 articles that have a given tag. How do I create this complicated join in SQLAlchemy?

In SQL it would look like:

SELECT a.article_id, a.title FROM Articles AS a
JOIN ArticleTags AS at ON a.article_id  = at.article_id
JOIN Tags        AS t  ON at.tag_id = t.tag_id
WHERE t.name = 'tag_name'

I can't figure out how to do it in SQLAlchemy. I am using ArticleTags as "secondary" table only and I can't figure out how to involve it in the JOIN.

Can anyone help?

Thanks, Boda Cydo.

Answer 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.