Multi-Table Invoice SUM Comparison

Question

Say I have 3 tables in a rails app:

invoices

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.

invoice_items

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

invoice_payments

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

This represents 4 orders:

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.

The second has only one item, and one payment, both totaling 1.25. This order is paid in full.

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.

The final order has one item again, for a total of 1.25, but no payments as of yet.

Now I need a query:

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.

I can do it in pure sql:

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 < total_amount

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 ActiveRecord::Relation, 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.

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.

The closest I've gotten so far was this:

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 < total')

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.

Any thoughts here?

Answer 1

You can get your results using group by and having clause of MySQL as:

Pure MySQL Query:

  SELECT `invoices`.* FROM `invoices` 
  INNER JOIN `invoice_items` ON 
    `invoice_items`.`invoice_id` = `invoices`.`id` 
  INNER JOIN `invoice_payments` ON 
    `invoice_payments`.`invoice_id` = `invoices`.`id` 
  GROUP BY invoices.id 
    HAVING sum(invoice_items.price) < sum(invoice_payments.amount) 

ActiveRecord Query:

Invoice.joins(:invoice_items, :invoice_payments).group("invoices.id").having("sum(invoice_items.price) < sum(:invoice_payments.amount)")

Answer 2

When building more complex queries in Rails usually Arel Really Exasperates Logicians comes in handy

Arel is a SQL AST manager for Ruby. It

1. simplifies the generation of complex SQL queries, and
2. adapts to various RDBMSes.

Here is a sample how the Arel implementation would look like based on the requirements

invoice_table = Invoice.arel_table

# Define invoice_payment_amounts
payment_arel_table = InvoicePayment.arel_table
invoice_payment_amounts = Arel::Table.new(:invoice_payment_amounts)
payment_cte = Arel::Nodes::As.new(
  invoice_payment_amounts,
  payment_arel_table
    .project(payment_arel_table[:invoice_id],
             payment_arel_table[:amount].sum.as("amount_paid"))
    .group(payment_arel_table[:invoice_id])
)

# Define invoice_item_amounts
item_arel_table = InvoiceItem.arel_table
invoice_item_amounts =  Arel::Table.new(:invoice_item_amounts)
item_cte = Arel::Nodes::As.new(
  invoice_item_amounts,
  item_arel_table
    .project(item_arel_table[:invoice_id],
             item_arel_table[:price].sum.as("total"))
    .group(item_arel_table[:invoice_id])
)

# Define main query
query = invoice_table
          .project(
            invoice_table[Arel.sql('*')],
            invoice_payment_amounts[:amount_paid],
            invoice_item_amounts[:total]
          )
          .join(invoice_payment_amounts).on(
            invoice_table[:id].eq(invoice_payment_amounts[:invoice_id])
          )
          .join(invoice_item_amounts).on(
            invoice_table[:id].eq(invoice_item_amounts[:invoice_id])
          )
          .where(invoice_item_amounts[:total].gt(invoice_payment_amounts[:amount_paid]))
          .with(payment_cte, item_cte)


res = Invoice.find_by_sql(query.to_sql)
for r in res do
  puts "---- Invoice #{r.id} -----"
  p r
  puts "total: #{r[:total]}"
  puts "amount_paid: #{r[:amount_paid]}"
  puts "----"
end

This will return the same output as your SQL query using the sample data you have provided to the question. Output:

 <Invoice id: 2, notes: "An order with 1 items.", created_at: "2017-12-18 21:15:47", updated_at: "2017-12-18 21:15:47">
 total: 2.5
 amount_paid: 1.25
 ----
 ---- Invoice 1 -----
 <Invoice id: 1, notes: "An order with 2 items.", created_at: "2017-12-18 21:15:47", updated_at: "2017-12-18 21:15:47">
 total: 12.6
 amount_paid: 6.1
 ----

Arel is quite flexible so you can use this as a base and refine the query conditions based on more specific requirements you might have.

I would strongly recommend for you to consider creating a cache columns (total, amount_paid) in the Invoice table and maintain them so you can avoid this complex query. At least the total additional column would be quite simple to create and fill the data.