title: RAILS SQL Subquery optimization
body: <p>I have a query which looks like this:</p>
  
  <pre><code>@inventory =  Pack.find_by_sql("SELECT Packs.id, "+
  " (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'online'      AND Stocks.user_id = #{current_user.id})) AS online,"+
  " (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'offline'     AND Stocks.user_id = #{current_user.id})) AS offline,"+
  " (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'depositing'  AND Stocks.user_id = #{current_user.id})) AS depositing,"+
  " (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'withdrawing' AND Stocks.user_id = #{current_user.id})) AS withdrawing,"+
  " (SELECT COUNT(*) FROM Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.status = 'selling'     AND Stocks.user_id = #{current_user.id})) AS selling,"+
  " (SELECT COUNT(*) FROM Transactions WHERE (Transactions.pack_id = Packs.id AND Transactions.status = 'buying' AND Transactions.buyer_id = #{current_user.id})) AS buying"+
  " FROM Packs WHERE disabled = false")
  </code></pre>
  
  <p>I am thinking there's a way to make a new sub-query so that instead of</p>
  
  <pre><code>SELECT FROM Stocks
  </code></pre>
  
  <p>the query selects from a stored table </p>
  
  <pre><code>SELECT FROM (Stocks WHERE (Stocks.pack_id = Packs.id AND Stocks.user_id = #{current_user.id}))
  </code></pre>
  
  <p>which would only be queried <em>once</em>. Then the <code>WHERE Stocks.status = ?</code> stuff would be applied to that stored table.</p>
  
  <p>Any help guys?</p>