Matthew Moisen Matthew Moisen - 2 months ago 7
Python Question

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

I wish to execute the following SQL:

SELECT c.name, count(1)
FROM post p LEFT JOIN category_post cp ON (p.id = cp.post_id)
LEFT JOIN category c ON (cp.category_id = c.id)
WHERE 1=1
AND post.is_published = 1
GROUP BY c.name;


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(Category.name, func.count(1)) \
.outerjoin(CategoryPost) \
.outerjoin(Post) \
.filter(Post.is_published == True) \
.group_by(Category.name)


The SQL generated by this is:

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


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

Use select_from():

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