Query

All sites of a group, by country, role and function

The sites of the legal entities that belong to a group now, with the country of each, its role (headquarters, registered office, branch) and every function stated for it. A group URN or name; empty shows every group.

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

WITH m AS (  -- the memberships asked for: those of the group named (URN or name), or every one; as v_membership
    SELECT canonical_of(x.entity_id) AS entity_id, canonical_of(x.group_id) AS group_id, x.group_id AS group_row
    FROM membership x
    WHERE upper(x.valid_during) IS NULL OR upper(x.valid_during) > now()
), named AS (
    SELECT m.*, gn.name AS group_name FROM m LEFT JOIN v_subject_name gn ON gn.subject_id = m.group_row
    WHERE trim(%(group)s) = ''
       OR m.group_id = (SELECT canonical_of(subject_of_urn(%(group)s)))
       OR lower(gn.name) = lower(trim(%(group)s))
), t AS (  -- the tree's rows of the sites below those entities (v_tree's rules, only these nodes)
    SELECT * FROM tree_rows(ARRAY(SELECT subject_id FROM tree_children(ARRAY(SELECT DISTINCT entity_id FROM named))))
    WHERE kind = 'site' AND relation = 'site_of'
), v AS (  -- the sites' role and functions, read for these sites only
    SELECT * FROM current_values_now(ARRAY['urn:matchory:attribute:profile:site_function',
                                           'urn:matchory:attribute:profile:site_role'], ARRAY(SELECT subject_id FROM t))
), fn AS (
    SELECT subject_id, string_agg(DISTINCT value #>> '{}', ', ' ORDER BY value #>> '{}') AS site_functions
    FROM v WHERE attribute_urn = 'urn:matchory:attribute:profile:site_function' GROUP BY subject_id
)
SELECT subject_urn(n.group_id) AS group_urn, n.group_name, subject_urn(n.entity_id) AS entity_urn,
       en.name AS entity_name, t.urn AS site_urn, t.name AS site_name, pl.country, pl.city,
       ro.value #>> '{}' AS site_role, fn.site_functions
FROM named n
JOIN t ON t.parent_id = n.entity_id
LEFT JOIN v_subject_name en ON en.subject_id = n.entity_id
LEFT JOIN current_addresses(ARRAY(SELECT subject_id FROM t)) pl ON pl.subject_id = t.subject_id
LEFT JOIN fn ON fn.subject_id = t.subject_id
LEFT JOIN v ro ON ro.subject_id = t.subject_id AND ro.attribute_urn = 'urn:matchory:attribute:profile:site_role'
ORDER BY n.group_name, pl.country, site_role, site_functions, site_name