Question 113838 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – How to query optimize many to many association with SQL?

Scenario:

Invoice has many purchase_orders and purchase_orders has many invoices. Intermediate table is npayment_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.archived? }
    # 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.

Tags:

2

Answers


  1. 0

    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

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search