How to change the order of tables in a LEFT JOIN clause with SQLAlchemy
<p>I wish to execute the following SQL:</p>

<pre><code>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;
</code></pre>

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

<p>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:</p>

<pre><code>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)
</code></pre>

<p>The SQL generated by this is:</p>

<pre><code>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
</code></pre>

<p>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.</p>

<p>Use <a href="http://docs.sqlalchemy.org/en/latest/orm/query.html#sqlalchemy.orm.query.Query.select_from" rel="nofollow"><code>select_from()</code></a>:</p>

<pre><code>session.query(Category.name, func.count(1)) \
       .select_from(Post) \
       .outerjoin(CategoryPost) \
       .outerjoin(Category) \
       ...
</code></pre>

python sqlalchemy