Rails/SQL: finding invoices by checking two sums

Question

I have an Invoice model that has_many lines and has_many payments.

Invoice:
  id
  ref

Line:
  invoice_id:
  total (decimal)

Payment:
  invoice_id:
  total(decimal)

I need to find all paid invoices. So I'm doing the following:

Invoice.joins(:lines, :payments).having(' sum(lines.total) = sum(payments.total').group('invoices.id')

Which queries:

SELECT *
FROM "invoices"
INNER JOIN "lines" ON "lines"."invoice_id" = "invoices"."id"
INNER JOIN "payments" ON "payments"."invoice_id" = "invoices"."id"
GROUP BY invoices.id
HAVING sum(lines.total) = sum(payments.total)

But it always return empty array even if there are invoices fully paid.

Is something wrong with my code?

So "paid invoices" are defined .. how? `sum(total)` of all connected rows in `line` equals `sum(total)` of all connected rows in `payment`? — Erwin Brandstetter, Aug 28 '14 at 23:22

score 0 · Answer 1 · edited May 23 '17 at 12:12

If you join to more than one table with a 1:n relationship, the joined rows can multiply each other.
This related answer has more detailed explanation for the problem:

Two SQL LEFT JOINS produce incorrect result

To avoid that, sum the totals before you join. This way you join to exactly 1 (or 0) rows, and nothing is multiplied. Not only correct, also considerably faster.

SELECT i.*, l.sum_total
FROM   invoices i
JOIN  (
   SELECT invoice_id, sum(total) AS sum_total
   FROM   lines
   GROUP  BY 1
   ) l ON l.invoice_id = i.id
JOIN  (
   SELECT invoice_id, sum(total) AS sum_total
   FROM   payments
   GROUP  BY 1
   ) p ON p.invoice_id = i.id 
WHERE l.sum_total = p.sum_total;

Using [INNER] JOIN, not LEFT [OUTER] JOIN on purpose. Invoices that do not have any lines or payments are not of interest to begin with. Since we want "paid" invoices. For lack of definition and by the looks of the provided query, I am assuming that means invoices with actual lines and payments, both totaling the same.

Jaugar Chang · Answer 2 · 2014-08-29T11:56:35.947

If one invoice have a line and two payments fully paid like this:

lines:
id      total   invoice_id
1          30            1

payments:
id      total   invoice_id
1          10            1
2          20            1

Then join lines and payments to invoice with invoce_id will get 2 rows like this:

payment_id payment_total   line_id  line_total  invoice_id
1                     10         1          30           1
2                     20         1          30           1

So the sum of line_total will not equal to sum of payment_total.

To get all paid invoice could use exists instead of joins:

Invoice.where(
       "exists 
        (select 1 from 
          (select invoice_id
           from (select invoice_id,sum(total) as line_total
               from lines
               group by invoice_id) as l
           inner join (select invoice_id,sum(total) as payment_total
               from payments
               group by invoice_id) as p
           on l.invoice_id = p.invoice_id
           where payment_total = line_total) as paid
         where invoices.id = paid.id) ")

The sub_query paid will get all paid invoice_ids.

Rails/SQL: finding invoices by checking two sums

2 Answers2