Querying more than one template (JOIN)

Specifications AQL
1

Probably this is an overkill or there is somebody out there who has faced the same issue.
I am trying to perform a simple “join” (as per SQL terms) to count patients that are contained in the 3 different templates (different archetypes), but I cannot seem to get a response. I am not sure of the behaviour hence seeking for help here, hopefully someone can guide me a.

However, if I try the count on individual templates, I do get the desired result.

I am using Better openEHR Platform.

SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers
FROM EHR e
CONTAINS COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0]
CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.progress_note.v0]
CONTAINS COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1]
WHERE d/name/value='Person' AND c/archetype_details/template_id/value='Follow-up' AND f/archetype_details/template_id/value='Echokardiographie'
OFFSET 0 LIMIT 5

Thank you.

2 
SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers
FROM EHR e
CONTAINS COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0]
AND CONTAINS COMPOSITION c[openEHR-EHR-OR COMPOSITION.progress_note.v0]
AND CONTAINS COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1]
WHERE d/name/value='Person' AND c/archetype_details/template_id/value='Follow-up' AND f/archetype_details/template_id/value='Echokardiographie'

Try adding the ‘and’ clauses as the current AQL implies Nesting of contains which is actually impossible.

Having said that, I’ m not sure you really need to be ‘joining’ this way - what is the actual question you are trying to answer?

3

Thanks @ian.mcnicoll .

Well, my actual question/request is how can I count the number of subjects (Patient_numbers) that have compositions created based on 3 different templates. I have 3 templates; demographics (Person), and the other 2 (Reports).

Possible that I have (Template 1)350 Subjects; but (Template 2) 40 progress_notes and (Template 3) 30 reports.

So to get the right count numbers for the scenario described above, I need to do something related to “nesting” or joining I am assuming.

I hope this clarifies my question. Will be glad for any help or guidance.

Thanks,

Michael

4

You could also try

CONTAINS (COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0]
AND COMPOSITION c[openEHR-EHR-OR COMPOSITION.progress_note.v0]
AND COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1])
5

Unfortunately this results into “Query is not valid”

SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers
FROM EHR e
CONTAINS (COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0]
 AND COMPOSITION c[openEHR-EHR-OR COMPOSITION.progress_note.v0]
 AND COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1])
WHERE d/archetype_details/template_id/value='Person' AND c/archetype_details/template_id/value='Follow-up' AND f/archetype_details/template_id/value='Echokardiographie'
6

I tried to “simplify” the query with just two templates and using ‘value IN’ but still the nesting is not working out. probably my syntax is wrong. Will be glad for any suggestions.

SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers FROM EHR e CONTAINS COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0] WHERE e/ehr_status/subject/external_ref/id/value IN (SELECT (DISTINCT e/ehr_status/subject/external_ref/id/value) FROM EHR e CONTAINS COMPOSITION c[openEHR-EHR-OR COMPOSITION.progress_note.v0] WHERE c/archetype_details/template_id/value='Follow-up') AND d/archetype_details/template_id/value='Person'

7

This is working for me on BetterCDR

SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers

FROM EHR e

CONTAINS (COMPOSITION a[openEHR-EHR-COMPOSITION.adverse_reaction_list.v1] and COMPOSITION m[openEHR-EHR-COMPOSITION.medication_list.v0])

WHERE a/name/value='Adverse reaction list' and m/name/value='Medication statement list'
1 Like
8

Yes, you are right. With 2 Templates it works. So for now I will just stick to querying with the two templates.
Thanks for the help.

` SELECT COUNT(DISTINCT e/ehr_status/subject/external_ref/id/value) as Patient_Numbers

FROM EHR e

CONTAINS (COMPOSITION d[openEHR-EHR-COMPOSITION.personendaten.v0] AND COMPOSITION f[openEHR-EHR-COMPOSITION.report.v1])

WHERE d/archetype_details/template_id/value='Person' AND f/archetype_details/template_id/value='Echokardiographie'`
9

I’d expect it to work with more than 2 templates

1 Like
10

Yes, it does. One of my template just had issues.

Thanks a bunch @ian.mcnicoll

11

Welcome to my world!! Glad you got it working!!

2 Likes
12

Was that issue related with the template syntax or semantics? just curious.

13

Hi Pablo,
It was a simple syntax issue in the naming.

1 Like