I made this SQL query and got sale orders that I needed, but I still need to join this sale orders with their invoices and filter only those sale orders that have invoices with state != 'draft'.
but can't find a way to join with sale orders with invoices.
I think there is table called sale_order_invoice_rel but can't figure out how to join and filter correctly
SELECT DISTINCT so.id
FROM stock_picking sp
INNER JOIN stock_move sm
ON sp.id = sm.picking_id
INNER JOIN procurement_order po
ON sm.procurement_id = po.id
INNER JOIN sale_order_line sol
ON po.sale_line_id = sol.id
INNER JOIN sale_order so
ON sol.order_id = so.id
WHERE so.invoice_status = 'to invoice' and sp.state = 'done'
ORDER BY so.id ASC
