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?
2 Answers
Answers 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)") Answers 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
- simplifies the generation of complex SQL queries, and
- 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.
0 comments:
Post a Comment