Query

Suppliers of a product with a site in one country and a parent in another

A site that states the product (a case-insensitive text match on the product name, by that site itself, not pooled) and lies in the site country, belonging to an entity that an active parent entity in the parent country controls (its direct parent, or a company that controls it directly or indirectly). Each condition is shown with the subject that met it.

product (text) site_country (text) parent_country (text) The SQL (always shown and editable; runs on a read-only connection with a 5 second limit and at most 500 rows)

WITH product AS MATERIALIZED (  -- the product statements, read once (each step is read once, then joined)
    SELECT c.subject_id, c.value ->> 'name' AS product FROM v_current c
    WHERE c.attribute_urn = 'urn:matchory:attribute:profile:product'
      AND trim(%(product)s) <> '' AND strpos(lower(c.value ->> 'name'), lower(trim(%(product)s))) > 0
),
stated AS MATERIALIZED (  -- the sites among them in the site country
    SELECT site.subject_id, site.urn, c.product, upper(pl.country) AS site_country
    FROM product c
    JOIN v_subject site ON site.subject_id = c.subject_id AND site.kind = 'site'
    JOIN v_subject_place pl ON pl.subject_id = site.subject_id
    WHERE upper(pl.country) = upper(trim(%(site_country)s))
),
function AS MATERIALIZED (  -- the site's role and every function stated for it
    SELECT f.subject_id,
           min(f.value #>> '{}') FILTER (WHERE f.attribute_urn = 'urn:matchory:attribute:profile:site_role') AS role,
           string_agg(DISTINCT f.value #>> '{}', ', ' ORDER BY f.value #>> '{}')
               FILTER (WHERE f.attribute_urn = 'urn:matchory:attribute:profile:site_function') AS functions
    FROM current_values_now(ARRAY['urn:matchory:attribute:profile:site_function',
                                  'urn:matchory:attribute:profile:site_role'],
                            ARRAY(SELECT subject_id FROM stated)) f
    GROUP BY f.subject_id
)
SELECT sup.urn AS supplier, sup.name AS supplier_name,
       st.product, st.urn AS product_and_site_stated_by, st.site_country,
       fn.role AS site_role, fn.functions AS site_functions,
       par.urn AS parent_met_by, par.name AS parent_name, upper(ppl.country) AS parent_country,
       CASE r.kind WHEN 'parent_of' THEN 'direct parent' ELSE 'controlled by' END AS how
FROM stated st
JOIN relation_state so ON so.kind = 'site_of' AND so.status = 'active'
                       AND (so.valid_during IS NULL OR so.valid_during @> now())
                       AND canonical_of(so.from_subject_id) = st.subject_id
JOIN v_subject sup ON sup.subject_id = canonical_of(so.to_subject_id) AND subject_status(sup.subject_id) = 'current'
LEFT JOIN function fn ON fn.subject_id = st.subject_id
JOIN relation_state r ON r.status = 'active' AND (r.valid_during IS NULL OR r.valid_during @> now())
                     AND ((r.kind = 'parent_of' AND canonical_of(r.to_subject_id) = sup.subject_id)
                          OR (r.kind = 'controlled_by' AND canonical_of(r.from_subject_id) = sup.subject_id))
JOIN v_subject par ON par.subject_id = canonical_of(CASE r.kind WHEN 'parent_of' THEN r.from_subject_id
                                                                ELSE r.to_subject_id END)
                  AND subject_status(par.subject_id) = 'current'
JOIN v_subject_place ppl ON ppl.subject_id = par.subject_id
WHERE upper(ppl.country) = upper(trim(%(parent_country)s))
ORDER BY supplier_name, st.urn, par.name, how