Postgresql – Postgres – Where condition on nested joins table not working

postgresql

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.

Best Answer

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;
Related Question