Probably subquerys and not joins

Question

This big query below returns me 2860 records and its correct number. I'm getting. The thing is that I need to add to this query invoice lines and make the same thing as I did with sale_order_lines "sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) AS price_subtotal". I need to get the sum of price_subtotal of invoice lines.

so my first thought was to join tables like this.

JOIN sale_order_invoice_rel so_inv_rel on (so_inv_rel.order_id = s.id )
JOIN account_invoice inv on (inv.id = so_inv_rel.invoice_id and inv.state in ('open','paid'))
JOIN account_invoice_line ail on (inv.id = ail.invoice_id)

and then

sum(ail.price_subtotal / COALESCE(cr.rate, 1.0)) as price_subtotal

but after the first JOIN number of lines, I'm selecting is changing and even if I joins are done the numbers are way off basically I get 5x2860. So probably I need to make some subquery but at this point, I don't know how and asking for help.

WITH currency_rate AS (
         SELECT r.currency_id,
            COALESCE(r.company_id, c.id) AS company_id,
            r.rate,
            r.name AS date_start,
            ( SELECT r2.name
                   FROM res_currency_rate r2
                  WHERE r2.name > r.name AND r2.currency_id = r.currency_id AND (r2.company_id IS NULL OR r2.company_id = c.id)
                  ORDER BY r2.name
                 LIMIT 1) AS date_end
           FROM res_currency_rate r
             JOIN res_company c ON r.company_id IS NULL OR r.company_id = c.id
        )
 SELECT min(l.id) AS id,
    l.product_id,
    l.color_id,
    l.product_size_id,
    t.uom_id AS product_uom,
    sum(l.product_uom_qty / u.factor * u2.factor) AS product_uom_qty,
    sum(l.qty_delivered / u.factor * u2.factor) AS qty_delivered,
    sum(l.qty_invoiced / u.factor * u2.factor) AS qty_invoiced,
    sum(l.qty_to_invoice / u.factor * u2.factor) AS qty_to_invoice,
    sum(l.price_total / COALESCE(cr.rate, 1.0)) AS price_total,
    l.price_unit / COALESCE(cr3.rate, 1.0) AS price_total_by_cmp_curr,
    sum(l.price_subtotal / COALESCE(cr.rate, 1.0)) AS price_subtotal,
    count(*) AS nbr,
    s.date_order AS date,
    s.state,
    s.partner_id,
    s.user_id,
    s.company_id,
    date_part('epoch'::text, avg(date_trunc('day'::text, s.date_order) - date_trunc('day'::text, s.create_date))) / (24 * 60 * 60)::numeric(16,2)::double precision AS delay,
    t.categ_id,
    s.pricelist_id,
    s.project_id AS analytic_account_id,
    s.team_id,
    p.product_tmpl_id,
    partner.country_id,
    partner.commercial_partner_id
   FROM sale_order_line l
     JOIN sale_order s ON l.order_id = s.id
     JOIN res_partner partner ON s.partner_id = partner.id
     LEFT JOIN product_product p ON l.product_id = p.id
     LEFT JOIN product_template t ON p.product_tmpl_id = t.id
     LEFT JOIN product_uom u ON u.id = l.product_uom
     LEFT JOIN product_uom u2 ON u2.id = t.uom_id
     LEFT JOIN res_company rc ON rc.id = s.company_id
     LEFT JOIN product_pricelist pp ON s.pricelist_id = pp.id
     LEFT JOIN currency_rate cr ON cr.currency_id = pp.currency_id AND cr.company_id = s.company_id AND cr.date_start <= COALESCE(s.date_order::timestamp with time zone, now()) AND (cr.date_end IS NULL OR cr.date_end > COALESCE(s.date_order::timestamp with time zone, now()))
     LEFT JOIN currency_rate cr3 ON cr.currency_id = rc.currency_id AND cr.company_id = s.company_id AND cr.date_start <= COALESCE(s.date_order::timestamp with time zone, now()) AND (cr.date_end IS NULL OR cr.date_end > COALESCE(s.date_order::timestamp with time zone, now()))
  GROUP BY l.product_id, t.uom_id, t.categ_id, s.date_order, s.partner_id, s.user_id, s.state, s.company_id, s.pricelist_id, s.project_id, s.team_id, l.color_id, cr3.rate, l.price_unit, l.product_size_id, p.product_tmpl_id, partner.country_id, partner.commercial_partner_id;

Answer 1

You can add the part that could be in a subquery to the with statement you already have to avoid the increase in number of lines, like so:

WITH currency_rate AS (
         SELECT r.currency_id,
            COALESCE(r.company_id, c.id) AS company_id,
            r.rate,
            r.name AS date_start,
            ( SELECT r2.name
                   FROM res_currency_rate r2
                  WHERE r2.name > r.name AND r2.currency_id = r.currency_id AND (r2.company_id IS NULL OR r2.company_id = c.id)
                  ORDER BY r2.name
                 LIMIT 1) AS date_end
           FROM res_currency_rate r
             JOIN res_company c ON r.company_id IS NULL OR r.company_id = c.id
        )
 , order_line_subtotal AS(
            SELECT so_inv_rel.order_id, sum(ail.price_subtotal) as price_subtotal
            FROM sale_order_invoice_rel so_inv_rel
            JOIN account_invoice inv on (inv.id = so_inv_rel.invoice_id and inv.state in ('open','paid'))
            JOIN account_invoice_line ail on (inv.id = ail.invoice_id)
            GROUP BY so_inv_rel.order_id )
 SELECT min(l.id) AS id,
 ....

From there it should be straightforward to add to the query without increasing the number of rows (since from the joins you already have a row for each order before the aggregation / group by.