1

I have 3 tables: order_items, invoice_items and invoices

In order_items model:

has_many :invoice_items

P.S an order_item may or may not have any invoice items associated with it

In Invoices model:

has_many :invoice_items

In InvoiceItem model

belongs_to :order_item
belongs_to :invoice

order_items table has booked_quantity and 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:

Solution 1:

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

Solution 2:

(
  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 !!

Sample Data:

order_items table

id      booked_quantity   cancelled_quantity
1          10                    2

invoices table

id      state
1       cancelled

invoice_items table

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.

0

The inner join between invoices and invoice_items turns the outer join between order_items as invoice_items back into an inner join. You need an outer join for invoices as well. And then you need to move the restriction on the invoice state into the join condition.

SELECT oi.*
FROM order_items oi
  LEFT JOIN invoice_items ii
         ON ii.order_item_id = oi.id
  LEFT JOIN invoices inv 
         ON inv.id = ii.invoice_id AND inv.state <> 'cancelled'
GROUP BY oi.id
HAVING (booked_quantity - cancelled_quantity - COALESCE(SUM(invoice_items.quantity),0)) > 0
ORDER BY oi.id ASC;

Maybe an EXISTS query is what you are looking for:

select oi.*
from order_items oi
where exists (select ii.order_item_id
              from invoices inv 
                join invoice_items ii ON inv.id = ii.invoice_id 
              where ii.order_item_id = oi.id
                and inv.state <> 'cancelled'
              group by ii.order_item_id
              having booked_quantity - cancelled_quantity - coalesce(sum(ii.quantity), 0) > 0)
order by oi.id asc;
  • I tried doing this but still no success. This seems to be pretty straight forward. I don't know what am i missing – Akshay Goyal Apr 16 at 13:47
  • @AkshayGoyal: I am unsure what exactly you are after. Maybe an EXISTS query? – a_horse_with_no_name Apr 16 at 14:22
  • I have updated the question to include sample data as well as desired result. This should make things much more clearer. Can you please take another look? – Akshay Goyal Apr 16 at 17:56
0

For those who are stuck at this/similar problem to this..the idea is only those invoice items should be joined where invoice state is cancelled. If we have two separate join statements then invoice items will get joined even though invoice state is cancelled. So basically, joining of invoice items should have an inner query to filter out only concerned invoice items.

Here's the final solution that i implemented:

 SELECT DISTINCT oi.*
  FROM order_items oi
  LEFT OUTER JOIN
         (SELECT ii.* from invoice_items ii
         INNER JOIN invoices inv
         ON inv.id = ii.invoice_id AND inv.state != 'cancelled') uncancelled_ii
         ON uncancelled_ii.order_item_id = oi.id
  GROUP BY oi.id
  HAVING (booked_quantity - cancelled_quantity - COALESCE(SUM(uncancelled_ii.quantity), 0)) > 0
  ORDER BY oi.id ASC
  • This should return the same as the second query in my answer – a_horse_with_no_name Apr 18 at 13:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.