# Queries on multiple Evaluations in a Composition

**Category:** [Implementation](https://discourse.openehr.org/c/implem/39)
**Created:** 2024-02-06 11:45 UTC
**Views:** 366
**Replies:** 9
**URL:** https://discourse.openehr.org/t/queries-on-multiple-evaluations-in-a-composition/4910

---

## Post #1 by @Samuel_elmeas

We are using a template where there are  multiple `evaluations` of the same type (unbound amount)  within a single `composition`. To be more specific, we have an arbitrary amount of diagnosis, using the Problem/Diagnosis Evaluation archetype, within a template that's used for the corresponding form.  The problem arises in a separate part of the application where we want to show the list of all diagnosis (just the name of each). 

That means we need to query a field within each evaluation that has been added to the composition. It currently seems that the only way to get  this data is to retrieve the entire composition using  and then handle the parsing ourselves. Are we missing anything here? Can this somehow be done with AQL?

---

## Post #2 by @ian.mcnicoll

That is exactly what AQL is for..

1. This will return an array of problem_diagnosis objects for the selected ehr_id /value

```
SELECT g AS problem_diagnosis
FROM EHR e [ehr_id/value = '{{ehrId}}']
CONTAINS COMPOSITION c
CONTAINS EVALUATION g[openEHR-EHR-EVALUATION.problem_diagnosis.v1] 
```

You can actually go right down to leaf node.
```
SELECT g/data[at0001]/items[at0002]/value/value AS Problem_Diagnosis_name,
       g/data[at0001]/items[at0003]/value AS Date_time_clinically_recognised
FROM EHR e [ehr_id/value = '{{ehrId}}']
CONTAINS COMPOSITION c
CONTAINS EVALUATION g[openEHR-EHR-EVALUATION.problem_diagnosis.v1]
```

but you might start to find duplicates rows being created with more complex queries ( the Cartesian product problem) if the nodes selected are multiple occurence.

---

## Post #3 by @ian.mcnicoll

BTW this has been setup to find problem/diagnosis entries in **any** composition. You might want to put more constraints in to control exactly where the AQL should find your 'master problem list'.

e.g.

```sql
SELECT m/data[at0001]/items[at0002]/value AS problem_diagnosis_name,
       m/data[at0001]/items[at0003]/value AS Date_time_clinically_recognised
FROM EHR e[ehr_id/value = '{{ehrId}}']
CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.problem_list.v1] 
CONTAINS EVALUATION m[openEHR-EHR-EVALUATION.problem_diagnosis.v1] 
```

or

```sql
SELECT m/data[at0001]/items[at0002]/value AS problem_diagnosis_name,
       m/data[at0001]/items[at0003]/value AS Date_time_clinically_recognised
FROM EHR e[ehr_id/value = '{{ehrId}}']
CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.problem_list.v1] 
CONTAINS EVALUATION m[openEHR-EHR-EVALUATION.problem_diagnosis.v1] 
WHERE c/archetype_details/template_id/value = 'My Template Id'
```

---

## Post #4 by @DavidD

@ian.mcnicoll 

Thanks for the quick response! 

I have the following query: 
```
SELECT 
c/uid/value, g/data[at0001]/items[at0002]/value/value AS problem_diagnosis 
FROM EHR e [ehr_id/value = '<EHR ID>'] 
CONTAINS COMPOSITION c[openEHR-EHR-COMPOSITION.problem_list-MVP_diagnosis_list.v2] 
CONTAINS EVALUATION g[openEHR-EHR-EVALUATION.problem_diagnosis.v1] 
WHERE c/archetype_details/template_id/value='<Template ID>'
```

Which gives the following response: 

```
"rows": [
  [
    "<Composition ID>",
    "Cholera"
  ],
]
```

However that particular composition has multiple Evaluations submitted, so I was expecting to see more data than that. It seems to only be returning the first instance of the matched data rather than the entire dataset. 

If I query the entire composition I can see all of the data is there but the above query doesn't return it all. 

Is there a way to update the query to return all of the data?

---

## Post #5 by @ian.mcnicoll

I'd expect that to return multiple rows if they exist - which CDR are you using?

---

## Post #6 by @DavidD

@ian.mcnicoll 

We're using EHRbase, though potentially an older version. 
So if this is expected to work then maybe it was fixed recently?

---

## Post #7 by @birger.haarbrandt

@DavidD this is a known bug. We are currently working on the 1.0 open source release with the all new AQL engine which will resolve this issue among others. Should drop around April

---

## Post #8 by @DavidD

@birger.haarbrandt 

Thank you for the response. 

Is there somewhere I can keep up to date with the progress of 1.0? I had a quick look at GitHub and couldn't see anything. 

Either way thanks again for letting me know

---

## Post #9 by @birger.haarbrandt

The changes are currently done in a private repo which is why it does not look like there is much activity (there surely is). Thing is that replacing the AQL engine is something that cannot be merged halfway. Soon the current codebase will be replaced in one go. I think the forum is the best places to get an update from time to time if there is any further delay.

---

## Post #10 by @jake.smolka

I suppose you can also use the RSS feed to get notified on releases: https://github.com/ehrbase/ehrbase/releases.atom

---


**Canonical:** https://discourse.openehr.org/t/queries-on-multiple-evaluations-in-a-composition/4910
**Original content:** https://discourse.openehr.org/t/queries-on-multiple-evaluations-in-a-composition/4910