title: Mysql nested left joins with counts
body: <p>I have a set of MySQL three tables in a "has many" relationship: deals, orders, and coupons.</p>
  
  <pre><code>Deals
  |----|--------------|
  | id |    title     |
  |----|--------------|
  |  1 | Some deal    |
  |  2 | Another deal |
  |----|--------------|
  
  Orders
  |----|---------|-----------|
  | id | deal_id |   state   |
  |----|---------|-----------|
  |  1 |       1 | purchased |
  |  2 |       1 | purchased |
  |  3 |       1 | expired   |
  |  4 |       2 | purchased |
  |----|---------|-----------|
  
  Coupons
  |----|----------|
  | id | order_id |
  |----|----------|
  |  1 |        1 |
  |  2 |        1 |
  |  3 |        1 |
  |  4 |        2 |
  |  5 |        2 |
  |  6 |        4 |
  |----|----------|
  </code></pre>
  
  <p>So, deals have many orders, which have many coupons.</p>
  
  <p>What I'd like to do is select on the deals table while counting the number of purchased orders and coupons.</p>
  
  <p>I know how to get a count on paid orders already:</p>
  
  <pre><code>SELECT deals.*, count(orders.id) AS orders_purchased_count FROM deals
  LEFT JOIN orders ON deals.id=orders.deal_id AND orders.state='purchased'
  WHERE deal_id &lt; 3
  GROUP BY deals.id
  
  Deals
  |----|--------------|------------------------|
  | id |    title     | orders_purchased_count |
  |----|--------------|------------------------|
  |  1 | Some deal    |                      2 |
  |  2 | Another deal |                      1 |
  |----|--------------|------------------------|
  </code></pre>
  
  <p>Similarly, I can get a count of coupons for orders:</p>
  
  <pre><code>SELECT orders.*, count(coupons.id) AS coupons_count FROM orders
  LEFT JOIN coupons ON orders.id=couoons.orders_id
  WHERE orders.state='purchased'
  GROUP BY orders.id
  
  Orders
  |----|-----------|---------------|
  | id |   state   | coupons_count |
  |----|-----------|---------------|
  |  1 | purchased |             3 |
  |  2 | purchased |             2 |
  |  4 | purchased |             1 |
  |----|-----------|---------------|
  </code></pre>
  
  <p>My question is: <strong>How do I combine these so that I can add coupons_count next to orders_purchased_count?</strong></p>
  
  <pre><code>Deals
  |----|--------------|------------------------|---------------|
  | id |    title     | orders_purchased_count | coupons_count |
  |----|--------------|------------------------|---------------|
  |  1 | Some deal    |                      2 |             5 |
  |  2 | Another deal |                      1 |             1 |
  |----|--------------|------------------------|---------------|
  </code></pre>
  
  <p>The tricky thing, in my case, will be to run the <code>WHERE deal_id &lt; 3</code> filter when selecting from <code>deals</code> before I join on <code>orders</code> and to run the <code>WHERE orders.state='purchased'</code> filter when selecting from <code>orders</code> before I join on <code>coupons</code>. It's a large dataset and I don't want to load all my <code>orders</code> and <code>coupons</code> into memory for the purpose of joining.</p>
  
  <p>At a loss for how to do this.</p>