title: Multi-Table Invoice SUM Comparison
body: <p>Say I have 3 tables in a rails app:</p>
  
  <p><strong>invoices</strong></p>
  
  <pre><code>id  | customer_id  | employee_id  | notes
  ---------------------------------------------------------------
  1   | 1            | 5            | An order with 2 items.
  2   | 12           | 5            | An order with 1 item.
  3   | 17           | 12           | An empty order.
  4   | 17           | 12           | A brand new order.
  </code></pre>
  
  <p><strong>invoice_items</strong></p>
  
  <pre><code>id  | invoice_id  | price  | name
  ---------------------------------------------------------
  1   | 1           | 5.35   | widget
  2   | 1           | 7.25   | thingy
  3   | 2           | 1.25   | smaller thingy
  4   | 2           | 1.25   | another smaller thingy
  </code></pre>
  
  <p><strong>invoice_payments</strong></p>
  
  <pre><code>id  | invoice_id  | amount  | method      | notes
  ---------------------------------------------------------
  1   | 1           | 4.85    | credit card | Not enough 
  2   | 1           | 1.25    | credit card | Still not enough
  3   | 2           | 1.25    | check       | Paid in full
  </code></pre>
  
  <p>This represents 4 orders:</p>
  
  <p>The first has 2 items, for a total of 12.60. It has two payments, for a total paid amount of 6.10. This order is partially paid. </p>
  
  <p>The second has only one item, and one payment, both totaling 1.25. This order is paid in full.</p>
  
  <p>The third order has no items or payments. This is important to us, sometimes we use this case. It is considered paid in full as well.</p>
  
  <p>The final order has one item again, for a total of 1.25, but no payments as of yet.</p>
  
  <p>Now I need a query:</p>
  
  <p><em>Show me all orders that are not paid in full yet; that is, all orders such that the total of the items is greater than the total of the payments.</em></p>
  
  <p>I can do it in pure sql:</p>
  
  <pre><code>SELECT      invoices.*,
              invoice_payment_amounts.amount_paid AS amount_paid,
              invoice_item_amounts.total_amount AS total_amount
  FROM        invoices
  LEFT JOIN   (
                  SELECT      invoices.id AS invoice_id,
                              COALESCE(SUM(invoice_payments.amount), 0) AS amount_paid
                  FROM        invoices
                  LEFT JOIN   invoice_payments
                  ON          invoices.id = invoice_payments.invoice_id
                  GROUP BY    invoices.id
              ) AS invoice_payment_amounts
  ON          invoices.id = invoice_payment_amounts.invoice_id
  LEFT JOIN   (
                  SELECT      invoices.id AS invoice_id,
                              COALESCE(SUM(invoice_items.item_price), 0) AS total_amount
                  FROM        invoices
                  LEFT JOIN   invoice_items
                  ON          invoices.id = invoice_items.invoice_id
                  GROUP BY    invoices.id
              ) AS invoice_item_amounts
  ON          invoices.id = invoice_item_amounts.invoice_id
  WHERE       amount_paid &lt; total_amount
  </code></pre>
  
  <p>But...now I need to get that into rails (probably as a scope). I can use find_by_sql, but that then returns an array, rather than an <em>ActiveRecord::Relation</em>, which is not what I need, since I want to chain it with other scopes (there is, for example, an overdue scope, which uses this), etc.</p>
  
  <p>So raw SQL probably isn't the right way to go here.....but what is? I've not been able to do this in activerecord's query language.</p>
  
  <p>The closest I've gotten so far was this:</p>
  
  <pre><code>Invoice.select('invoices.*, SUM(invoice_items.price) AS total, SUM(invoice_payments.amount) AS amount_paid').
    joins(:invoice_payments, :invoice_items).
    group('invoices.id').
    where('amount_paid &lt; total')
  </code></pre>
  
  <p>But that fails, since on orders like #1, with multiple payments, it incorrectly doubles the price of the order (due to multiple joins), showing it as still unpaid. I had the same problem in SQL, which is why I structured it in the way I did.</p>
  
  <p>Any thoughts here?</p>