Need to know if an AQL query can fetch data across 2 different template/compositions built with different root archetypes?
e.g.,
SELECT e/ehr_id/value from ehr e CONTAINS COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS CLUSTER o[openEHR-EHR-CLUSTER.tnm.v1] AND COMPOSITION c2[openEHR-EHR-COMPOSITION.report.v1] CONTAINS observation o1[openEHR-EHR-OBSERVATION.progress_note.v1]
There is no output for the above query. We expected the AND to give the common EHR IDs that are present across these compositions. In the above case it is expected to give 4 EHR IDs (as per the data in the CDR).
To elaborate:
We have created 2 templates - one using the root archetype “openEHR-EHR-COMPOSITION.encounter.v1” and another using the root archetype of “openEHR-EHR-COMPOSITION.report.v1”. We have successfully inserted compositions using these templates. However we are unable to query across the archetypes belonging to these compositions.
@pablo Yes, That works. c1 gives x number of ehr-ids, and c2 gives y number of ehr-ids. x Intersection y contains 4 ehr-ids which was our expected output
Instead of AND, if we use OR, we get x+y rows - if we use the query like this:
SELECT e/ehr_id/value from ehr e CONTAINS COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v1] CONTAINS CLUSTER o[openEHR-EHR-CLUSTER.tnm.v1] OR COMPOSITION c2[openEHR-EHR-COMPOSITION.report.v1]
While trying out various combinations, I see that if I don’t give the root archetype id explicitly, it seems to return 6 ehr ids. I’m yet to verify the correctness of the data returned - thought I will post before doing the verification. Will update soon.
{“q” : “SELECT e/ehr_id/value from ehr e CONTAINS COMPOSITION c1 CONTAINS CLUSTER o[openEHR-EHR-CLUSTER.tnm.v1] AND COMPOSITION c2 CONTAINS observation o1[openEHR-EHR-OBSERVATION.progress_note.v1]”
}
Verified the data. We were supposed to get 4 ehrids but getting 6 ehrids. 4/6 are correct. Among the wrong ones, one ehrid is only available in composition c1 and another only in composition c2. These 2 were not supposed to be picked by the AQL as it is an AND.
I assume you are using a recent version of EHRbase? We are still aiming to catch all bug, hence please open an issue in the GitHub repo: Issues · ehrbase/ehrbase · GitHub
It is a bit difficult to tell right now if the expression should yield the expected result, but let’s check.
Can you please try the following? I wrote these from the top of my head, didn’t test etc, but I’m guessing they may do what you’re after.
SELECT
e/ehr_id/value
from ehr e
CONTAINS
(
(COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v1]
CONTAINS CLUSTER o[openEHR-EHR-CLUSTER.tnm.v1] )
AND (COMPOSITION c2[openEHR-EHR-COMPOSITION.report.v1]
CONTAINS observation o1[openEHR-EHR-OBSERVATION.progress_note.v1]))
The query above is playing it really safe. The next one is not as explicit as the above, but it may also work
SELECT
e/ehr_id/value
from ehr e
CONTAINS
(
COMPOSITION c1[openEHR-EHR-COMPOSITION.encounter.v1]
CONTAINS CLUSTER o[openEHR-EHR-CLUSTER.tnm.v1]
AND COMPOSITION c2[openEHR-EHR-COMPOSITION.report.v1]
CONTAINS observation o1[openEHR-EHR-OBSERVATION.progress_note.v1])
I suspected that the implementation may suffer from an ambiguity problem in the original query you provided. I thought It might have been unclear if you’re asking for
The first one is impossible as per the RM since there’s no way for a COMPOSITION to contain another COMPOSITION (if anybody reading this is inclined to get creative and respond with “but…”, don’t. You know what I mean here)
So I thought I’d make the second one explicit, so that an potential ambiguity problem at the implementation level can be worked around. If it didn’t work, then there’s something to look into indeed.
As we are still in beta, we cannot guarantee that there won’t be findings (yeah, bugs will happen even after release, but we made a deliberate decision to make the official release once we are feeling confident to meet our quality standards).
I actually thought the query itself is not correct. I didn’t check but using logical operators with CONTAINS should have a syntax in which the parenthesis are mandatory to avoid the ambiguity I was talking about.