Cardiovascular Disease Symptoms

WITH RECURSIVE
    subtypeids (sourceId) AS (
        -- This recursive CTE returns rows containing subtype SCTIDs
        SELECT r.sourceId
        FROM sct_relationship r
        WHERE r.destinationId IN ('49601007') -- Starting concepts (find all their children recursively) 
            AND r.typeId = '116680003'
            AND active = 1
        UNION ALL
        SELECT r.sourceId
        FROM sct_relationship r
                INNER JOIN subtypeids ON r.destinationId = subtypeids.sourceid
        WHERE r.active = 1
            AND r.typeId = '116680003'
    ),
    sctids (sourceId) AS (
        SELECT '49601007' as sourceId -- add starting concepts to get the final set of ids to find
        UNION 
        SELECT DISTINCT sourceId -- IMPORTANT: distinct is required
        FROM subtypeids
    ),
    patient_ers AS (
        SELECT er.id AS expid, e.type_ as mention, c.description as experiencer
        FROM experiencerrelation er
                JOIN entity e
                        ON er.experiencer_id = e.id -- NOTE: find experience(r) entities
                JOIN foundentity fe
                        ON fe.id = e.entity_id
                JOIN foundentityconcept fec
                        ON fe.id = fec.found_entity_id
                JOIN concept c
                        ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
        WHERE c.ontology = 'snomed'
            AND e.type_ = 'found'
            AND c.concept_id = '116154003' -- SCTID of 'Patient (person)'
        UNION
        SELECT er.id AS expid, e.type_ as mention, ae.value as experiencer
        FROM entity e
                JOIN experiencerrelation er
                        ON er.experiencer_id = e.id
                JOIN assumedentity ae
                        ON ae.id = e.entity_id
        WHERE e.type_ = 'assumed'
            AND ae.value = 'patient'
    )
    SELECT -- dm.subject_id           as subject_id,
        -- dm.chartdate            as chartdate, 
        -- regexp_replace(d.filename, '.*/', '')              as filename,
        c.description           as fully_specified_name,
        c.concept_id,
        patient_ers.experiencer as experiencer,
        fe.text                as term,
        sl.text                 as sentence
    FROM patient_ers
            JOIN experiencerrelation er ON patient_ers.expid = er.id
            JOIN entity e ON er.experienced_id = e.id
            JOIN foundentity fe ON fe.id = e.entity_id
            JOIN foundentityconcept fec ON fe.id = fec.found_entity_id
            JOIN concept c ON c.concept_id = fec.concept_id AND c.ontology = fec.concept_ontology
            JOIN foundentitylocation fel ON fe.id = fel.found_entity_id
            JOIN location l ON fel.location_id = l.id AND (l.type_ = 'sentence')
            JOIN sentencelocation sl ON sl.id = l.location_id AND (l.type_ = 'sentence')
            JOIN foundentitytype fet ON fet.found_entity_id = fe.id AND (fet.ontology LIKE 'snomed')
            JOIN document d ON d.id = l.document_id
            JOIN documentmetadata dm ON d.id = dm.document_id
    WHERE c.ontology = 'snomed'
    AND c.concept_id IN (SELECT * FROM sctids)
    AND fe.polarity = 'asserted';