Scenario:

Invoice has many purchase_orders and purchase_orders has many invoices. Intermediate table is invoice_links which has foreign key invoice_id, purchase_order_id.

Tech Stack

Rails 5.x, Postgresql

Here is my sample data

invoices

id	name	status
100	sample.pdf	archived
101	sample1.pdf	archived
102	sample2.pdf	archived
103	sample2.pdf	active
104	sample2.pdf	active

purchase_orders

id	title
1	first po
2	second po
3	third po
4	fourth po

npayment_links

id	purchase_order_id	invoice_id
1	1	100
2	1	101
3	1	102
4	2	100
5	2	103
6	3	104
7	4	100

I am expecting query which returns all purchase_orders whose all invoices are archived.

• If you see npayment_links
  • purchase_orders with id=1 is associated with 3 invoices (100, 101, 102), which has all archived invoices.
  • purchase_orders with id=2 is associated with 2 invoices (100, 103), which has archived and active invoices.
  • purchase_orders with id=3 is associated with 1 invoice (104), which has active invoice.
  • purchase_orders with id=4 is associated with 1 invoice (100), which has archived invoice.

I'm searching for Sql query which returns PO list which contains all archived invoices.

Expected purchase_orders

id	title
1	first po
4	fourth po

I have achieved above issue with Rails AR way. But, I'm searching for some Sql query to achieve this:

Invoice.find(100).purchase_orders.each do |po|
  if po.invoices.all? { |inv| inv.cancelled? }
    # po.update(status: :done) # I will do some operation here. And If there are 1000s of data in which each PO again have many invoices, I might feel this will add query complexity. So, I am searching for some optimized solution here.
  end
end

Any feedback would be appreciated.

CodePudding user response：

You need to use JOINS to connect your tables. Then you can simply query for all purchase orders that have archived invoices and compare that to second select with EXCEPT that would give you POs that have active invoices. By using EXCEPT as a result you will get all rows that appear in first select without those that appear in second select.

SELECT 
    po.* 
FROM 
    purchase_orders po
    JOIN npayment_links pl on po.id = pl.purchase_order_id
    JOIN invoices i on pl.invoice_id = i.id
WHERE i.status LIKE 'archived'
EXCEPT
SELECT 
    po.* 
FROM 
    purchase_orders po
    JOIN npayment_links pl on po.id = pl.purchase_order_id
    JOIN invoices i on pl.invoice_id = i.id
WHERE i.status LIKE 'active'

db<>fiddle

CodePudding user response：

Maybe not the most optimized solution but the following should work:

PurchaseOrder.where.not(id: 
  PurchaseOrder
    .select(:id)
    .joins(:invoices)
    .where.not(invoices: {status: 'archived'})
)

The thought process is find all the Purchase Orders who's id is not in a list of Purchase Order ids with a status that is something other than archived and this will result in the following SQL

SELECT 
  purchase_orders.* 
FROM 
  purchase_orders
WHERE 
  purchase_orders.id NOT IN (
    SELECT 
      purchase_orders.id
    FROM 
      purchase_orders
      INNER JOIN invoice_links ON invoice_links.purchase_order_id = purchase_orders.id 
      INNER JOIN invoices ON invoices.id = invoice_links.invoice_id 
    WHERE 
      invoices.status <> 'archived'
)

This will also return Purchase Orders that do not have any invoices. If having an invoice is also a requirement you can simply add joins(:invoices) e.g.

PurchaseOrder
  .joins(:invoices)
  .where.not(id: 
    PurchaseOrder
      .select(:id)
      .joins(:invoices)
      .where.not(invoices: {status: 'archived'})
)

Note: Your question states the Join Table is invoice_links and then references npayment_links so I am unsure which is the actual join table. For my example I will assume the join table is invoice_links as that makes more logical sense; however, provided the associations are setup correctly in the ORM, this assumption has no impact on the functionality of the proposed solution.