Left Ventricular Ejection Fraction

WITH measurementvalues AS (
    SELECT measurementrelation.id,
        string_agg( val_fe.text, ', ' ) AS values,
        string_agg(femu_val.text, ' x ') AS units
        FROM  measurementrelation
        -- 2. retrieve the entity attributes of the ** value ** of the measurement relation
        JOIN entity val_e
            ON measurementrelation.value_id = val_e.id AND val_e.type_ = 'found'
        JOIN foundentity val_fe
            ON val_e.entity_id = val_fe.id
        LEFT JOIN foundentitymeasurementunit femu_val -- retrieve units associated with a measurement value
            ON femu_val.found_entity_id = val_fe.id
    GROUP BY measurementrelation.id
)
SELECT  measurementrelation.id,
        -- if documentmetadata is populated
        -- dm.chartdate,
        -- dm.subject_id,
        REGEXP_REPLACE(sub_fe.text, '\n', ' ', 'g') AS subject,
        sub_c.concept_id                           AS concept,
        mv.values                                  AS value,
        mv.units                                   AS units,
        sl.text                                    AS sentence
FROM measurementrelation
        -- 1. retrieve the entity attributes of the ** subject ** of the measurement relation
        JOIN entity sub_e
            ON measurementrelation.subject_id = sub_e.id AND sub_e.type_ = 'found'
        JOIN foundentity sub_fe
            ON sub_e.entity_id = sub_fe.id
        JOIN foundentityconcept sub_fec
            ON sub_fec.found_entity_id = sub_fe.id
        JOIN concept sub_c
            ON sub_c.concept_id = sub_fec.concept_id AND sub_fec.concept_ontology = sub_c.ontology
        -- 2. get values and their units from CTE table
        JOIN measurementvalues mv ON mv.id = measurementrelation.id
        -- 3. retrieve sentence by joining with location and document tables on subject's foundentity id
        JOIN foundentitylocation sub_fel
            ON sub_fe.id = sub_fel.found_entity_id
        JOIN location l
            ON sub_fel.location_id = l.id
        JOIN document d
            ON l.document_id = d.id
        JOIN sentencelocation sl
            ON sl.id = l.location_id AND l.type_ = 'sentence'
        -- uncomment if documentmetadata is available
        -- JOIN documentmetadata dm 
        --    ON dm.document_id =  d.id
WHERE sub_c.ontology = 'snomed' -- Use SNOMED CT
      AND sub_c.concept_id = '250908004'; -- Snomed concept for Left Ventricular Ejection Fraction;

WITH lvef_mentions AS (
    -- retrieve lvef mentions
    SELECT fe.id as subject_feid, e.id as subject_eid, fe.text as subject_term FROM foundentity fe
        JOIN entity e on e.entity_id = fe.id AND e.type_ = 'found'
        JOIN foundentityconcept fec on fe.id = fec.found_entity_id
        JOIN concept c on fec.concept_id = c.concept_id and fec.concept_ontology = c.ontology
    WHERE
        c.ontology = 'snomed' AND c.concept_id = '250908004' -- LVEF 250908004
),
lvef_values AS (
-- retrieve values and units when present
SELECT mr.id as mrel_id,
        mr.subject_id as subject_eid,
        val_fe.id as value_feid,
        val_fe.text as value_term,
        string_agg( val_femu.text, ', ') as units
FROM measurementrelation mr
    JOIN lvef_mentions fm ON mr.subject_id = fm.subject_eid
    JOIN entity val_e on mr.value_id = val_e.id AND val_e.type_='found'
    JOIN foundentity val_fe ON val_e.entity_id = val_fe.id
    -- retrieve unit(s) for value term if present (using LEFT JOIN)
    LEFT JOIN foundentitymeasurementunit val_femu ON val_fe.id = val_femu.found_entity_id
GROUP BY mrel_id, mr.subject_id, value_feid, value_term
)
SELECT
    fv.mrel_id as id,
    fm.subject_term as subject,
    fv.value_term as value,
    fv.units as units,
    s.text as sentence
FROM lvef_mentions fm
    -- retrieve value terms if available (LEFT JOIN is required)
    LEFT JOIN lvef_values fv ON fm.subject_eid = fv.subject_eid
    -- retrieve sentence using the subject entity
    JOIN foundentitylocation fel on fm.subject_feid = fel.found_entity_id
    JOIN location l on fel.location_id = l.id AND l.type_='sentence'
    JOIN sentencelocation s on l.location_id = s.id;