title: How to join two queries in SQLAlchemy?
body: <p>In this example, I am using the sample <a href="http://www.mysqltutorial.org/mysql-sample-database.aspx" rel="nofollow">MySQL classicmodels database</a>.</p>
  
  <p>So I have two queries:</p>
  
  <pre><code>products = session.query(Products)
  orderdetails = session.query(OrderDetails)
  </code></pre>
  
  <p><strong>Let's assume I cannot make any more queries to the database after this and I can only join these two queries from this point on.</strong></p>
  
  <p>I want to do an outer join on them to be able to do something like this:</p>
  
  <pre><code>for orderdetail, product in query:
      print product.productName, product.productCode, orderdetails.quantityOrdered
  </code></pre>
  
  <p>However, whenever I do an outerjoin on this, I can only seem to get a left join.</p>
  
  <pre><code>query = orderdetails.outerjoin(Products)
  </code></pre>
  
  <p>Code like this yields only orderdetails columns:</p>
  
  <pre><code>for q in query:
      # Only gives orderdetails columns
      print q
  </code></pre>
  
  <p>And doing something like this:</p>
  
  <pre><code>for orderdetails, product in query:
      print orderdetails, product
  </code></pre>
  
  <p>Gives me an error: <code>TypeError: 'OrderDetails' object is not iterable</code>. </p>
  
  <p>What am I doing wrong? I just want columns from the Products table as well.</p>
  
  <p>EDIT:</p>
  
  <p>I have found my solution thanks to @univerio's answer. My real goal was to do a join on two existing queries and then do a <code>SUM</code> and <code>COUNT</code> operation on them.</p>
  
  <p>SQLAlchemy basically just transforms a query object to a SQL statement. The <code>with_entities</code> function just changes the <code>SELECT</code> expression to whatever you pass to it. This is my updated solution, which includes unpacking and reading the join:</p>
  
  <pre><code>for productCode, numOrders, quantityOrdered in orderdetails.with_entities(
          OrderDetails.productCode, 
          func.count(OrderDetails.productCode),
          func.sum(OrderDetails.quantityOrdered)).group_by(OrderDetails.productCode):
      print productCode, numOrders, quantityOrdered
  </code></pre>