Matthew Moisen Matthew Moisen - 1 year ago 48
Python Question

How to change the order of tables in a LEFT JOIN clause with SQLAlchemy

I wish to execute the following SQL:

SELECT, count(1)
FROM post p LEFT JOIN category_post cp ON ( = cp.post_id)
LEFT JOIN category c ON (cp.category_id =
AND post.is_published = 1

A post has 0 or more categories. This post will return the count of each category, including the uncategorized posts, as I LEFT JOIN from post-->category, as opposed to the other way around, which would not include the count of uncategorized posts.

In SQLAlchemy, I can get part of the solution using the following, however it doesn't return the count of uncategorized posts because the LEFT JOIN is going in the wrong direction:

from sqlalchemy.sql import func
q = session.query(, func.count(1)) \
.outerjoin(CategoryPost) \
.outerjoin(Post) \
.filter(Post.is_published == True) \

The SQL generated by this is:

SELECT AS category_name, count(1) AS count_1
FROM category LEFT OUTER JOIN category_post ON = category_post.category_id
LEFT OUTER JOIN post ON = category_post.post_id
WHERE post.is_published = 1

If I could change the order of the tables in the JOIN clause somehow, or even just do a RIGHT JOIN while keeping the same order, I could acccomplish my goal.

Answer Source

Use select_from():

session.query(, func.count(1)) \
       .select_from(Post) \
       .outerjoin(CategoryPost) \
       .outerjoin(Category) \