Open davidwatkins73 opened 3 years ago
To get hierarchy aware list of related measurables we need a query similar to :
with hierarchy as (select id from entity_hierarchy where kind = 'MEASURABLE' and ancestor_id = 39912),
rels as (
select * from entity_relationship
where (id_a in (select id from hierarchy) or (id_b in( select id from hierarchy ))) and kind_a = 'MEASURABLE' and kind_b = 'MEASURABLE'),
milestones as (select rels.*, m.milestone_definition_id, m.milestone_date from rels left join dw_milestone m on rels.id = m.entity_id and m.entity_kind = 'ENTITY_RELATIONSHIP'),
milestones_with_categories as (
select rels.*, mca.name a_cat_name, mcb.name b_cat_name
from milestones rels
inner join measurable ma on ma.id = rels.id_a
inner join measurable mb on mb.id = rels.id_b
inner join measurable_category mca on mca.id = ma.measurable_category_id
inner join measurable_category mcb on mcb.id = mb.measurable_category_id)
select case when id_a = 39912 or id_b = 39912 then 'direct' else 'indirect' end , mwc.*
from milestones_with_categories mwc;
We need to think about how the generic relationships can be better visualised.