I have 3 tables: order_items, invoice_items and invoices
In order_items model:
P.S an order_item may or may not have any invoice items associated with it
In Invoices model:
In InvoiceItem model
belongs_to :order_item belongs_to :invoice
order_items table has
cancelled_quantity fields in it
invoices table has
state field in it.
state can be cancelled or approved
invoice_items table has
quantity field in it.
An order item is considered to be invoiceable when the following condition holds:
(booked_quantity - cancelled_quantity - SUM(invoice_items.quantity where associated invoice is not cancelled)) > 0
I am trying to fetch all the order items which are invoiceable using single sql query. Here are the two solutions that i tried:
SELECT * FROM order_items LEFT OUTER JOIN invoice_items ON invoice_items.order_item_id = order_items.id INNER JOIN invoices ON invoices.id = invoice_items.invoice_id WHERE invoices.state != 'cancelled' GROUP BY order_items.id HAVING ((booked_quantity - cancelled_quantity - COALESCE(SUM(invoice_items.quantity))) > 0) ORDER BY order_items.id ASC
( WITH ii_ord_items AS ( SELECT invoice_items.order_item_id, COALESCE(SUM(invoice_items.quantity), 0) AS uncancelled_invoiced_quantity from order_items LEFT OUTER JOIN invoice_items ON invoice_items.order_item_id = order_items.id INNER JOIN invoices ON invoices.id = invoice_items.invoice_id WHERE invoices.state != 'cancelled' GROUP BY invoice_items.order_item_id ) SELECT order_items.* FROM order_items LEFT OUTER JOIN ii_oitems ON order_items.id = ii_oitems.order_item_id WHERE ( ii_ord_items.uncancelled_invoiced_quantity < (order_items.booked_quantity - order_items.cancelled_quantity)) ) AS order_items
Both of them are working fine when invoiced is not in cancelled state. But in case when invoice is cancelled both of them are giving incorrect results. They seem to be considering cancelled invoices quantity as well. So i guess there's some problem with the where condition but i couldn't figure out what's the issue exactly. Any help would be appreciated. Thanks in advance !!
id booked_quantity cancelled_quantity 1 10 2
id state 1 cancelled
id invoice_id order_item_id quantity 1 1 1 8
Expected Result: If i try to fetch all the order items for which invoice_items can be generated should include order_item with id 1 as well as invoice is currently cancelled. If the invoice state would have been anything other than "cancelled" then order_item with id 1 should not be present in the result.