One of the readers of Essential SQLAlchemy sent me an email with more questions about how .join() works.  In the example below, he wanted to know why join was only required for User, LineItem, and Cookie objects. Why isn’t Order required?

query = session.query(Order.order_id, User.username, User.phone,
                      Cookie.cookie_name, LineItem.quantity,
                      LineItem.extended_cost)
query = query.join(User).join(LineItem).join(Cookie)
results = query.filter(User.username == 'cookiemon').all()

To answer that question, lets take a look at the SQL generated by the ORM for our query.

query = session.query(Order.order_id, User.username, User.phone,
                      Cookie.cookie_name, LineItem.quantity,
                      LineItem.extended_cost)
print(query)

SELECT orders.order_id AS orders_order_id, 
       users.username AS users_username, 
       users.phone AS users_phone, 
       cookies.cookie_name AS cookies_cookie_name, 
       line_items.quantity AS line_items_quantity, 
       line_items.extended_cost AS line_items_extended_cost 
FROM orders, users, cookies, line_items

We can see that the FROM clause contains the Orders, Users, Cookies, and LineItems ORM objects __tablename__s for each object in the query. Also, notice the order is based on where they appeared in the SELECT clause. Just like in SQL, we need to define how the tables are related with JOIN clauses. These JOIN clauses need to follow the order of the relationships between the tables. This means we need to make sure that the table to the left of the JOIN clause has a relationship with the table in the .join() statement. This can be a bit confusing when we have chained .join() statements as shown in the first example. The table in the prior .join() statement to the left must have a relationship with the table in the current .join() statement that was being evaluated. Lets look at the SQL generated after all the .join() statements.

query = query.join(User).join(LineItem).join(Cookie)
print(query)


SELECT orders.order_id AS orders_order_id, 
       users.username AS users_username, 
       users.phone AS users_phone, 
       cookies.cookie_name AS cookies_cookie_name, 
       line_items.quantity AS line_items_quantity, 
       line_items.extended_cost AS line_items_extended_cost
FROM orders JOIN users ON users.user_id = orders.user_id 
     JOIN line_items ON orders.order_id = line_items.order_id 
     JOIN cookies ON cookies.cookie_id = line_items.cookie_id

We can see now that the FROM clause contains the JOIN clauses in the order we chained them into the query. So Order is the target of the first JOIN with User, which is why we didn’t have to have a .join() for it. You can see and play with this more with an ipython notebook available at https://github.com/jasonamyers/blog-12-31-15-join-question/blob/master/blog-join-example.ipynb.