WITH prescription AS (
SELECT DISTINCT(mr.id) as mr_id,
fe.id as fe_id,
e.document_id as doc_id,
c.description as description,
c.ontology as ontology,
c.concept_id as concept_id
FROM medicationrelation mr
-- get drug name
JOIN entity e ON mr.drug_entity_id = e.id
JOIN foundentity fe ON fe.id = e.entity_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 AND c.ontology='rxnorm'
),
indication AS (
SELECT DISTINCT(mri.medication_id) AS id,
string_agg(fe.text, ', ') AS indications
FROM medicationrelationindication mri
JOIN entity e ON mri.entity_id = e.id
JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
GROUP BY mri.medication_id
),
dose AS (
SELECT DISTINCT(mrd.medication_id) AS id,
string_agg(fe.text, ', ') AS dosages
FROM medicationrelationdosage mrd
JOIN entity e ON mrd.entity_id = e.id
JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
GROUP BY mrd.medication_id
),
frequency AS (
SELECT DISTINCT(mrf.medication_id) AS id,
string_agg(fe.text, ', ') AS frequencies
FROM medicationrelationfrequency mrf
JOIN entity e ON mrf.entity_id = e.id
JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
GROUP BY mrf.medication_id
),
quantity AS (
SELECT DISTINCT(mrq.medication_id) AS id,
string_agg(fe.text, ', ') AS quantities
FROM medicationrelationquantity mrq
JOIN entity e ON mrq.entity_id = e.id
JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
GROUP BY mrq.medication_id
),
modifier AS (
SELECT DISTINCT(mrmod.medication_id) AS id,
string_agg(fe.text, ', ') AS modifiers
FROM medicationrelationmodifier mrmod
JOIN entity e ON mrmod.entity_id = e.id
JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
GROUP BY mrmod.medication_id
),
duration AS (
SELECT DISTINCT(mrdur.medication_id) AS id,
string_agg(fe.text, ', ') AS durations
FROM medicationrelationduration mrdur
JOIN entity e ON mrdur.entity_id = e.id
JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
GROUP BY mrdur.medication_id
),
necessity AS (
SELECT DISTINCT(mrn.medication_id) AS id,
string_agg(fe.text, ', ') AS as_necessary
FROM medicationrelationnecessity mrn
JOIN entity e ON mrn.entity_id = e.id
JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
GROUP BY mrn.medication_id
),
date_time AS (
SELECT DISTINCT(mrdt.medication_id) AS id,
string_agg(fe.text, ', ') AS dates
FROM medicationrelationdatetime mrdt
JOIN entity e ON mrdt.entity_id = e.id
JOIN foundentity fe ON fe.id = e.entity_id AND e.type_ = 'found'
GROUP BY mrdt.medication_id
)
SELECT prescription.mr_id,
-- enable if documentmetadata is populated
-- dm.chartdate,
-- dm.subject_id,
prescription.description,
prescription.ontology,
prescription.concept_id,
indication.indications,
dose.dosages,
frequency.frequencies,
quantity.quantities,
modifier.modifiers,
duration.durations,
necessity.as_necessary,
date_time.dates,
sl.text as sentence
FROM prescription
LEFT JOIN indication ON prescription.mr_id = indication.id
LEFT JOIN dose ON prescription.mr_id = dose.id
LEFT JOIN frequency ON prescription.mr_id = frequency.id
LEFT JOIN quantity ON prescription.mr_id = quantity.id
LEFT JOIN modifier ON prescription.mr_id = modifier.id
LEFT JOIN duration ON prescription.mr_id = duration.id
LEFT JOIN necessity ON prescription.mr_id = necessity.id
LEFT JOIN date_time ON prescription.mr_id = date_time.id
-- Get sentence text
JOIN foundentitylocation fel ON fel.found_entity_id = prescription.fe_id
JOIN location l on fel.location_id = l.id AND l.type_ = 'sentence'
JOIN sentencelocation sl on l.location_id = sl.id
-- Get metadata
-- JOIN document d on d.id = l.document_id
-- JOIN documentmetadata dm on d.id = dm.document_id
WHERE indication.indications iLIKE '%pain%'
ORDER BY prescription.mr_id ASC;
