# AQL on folder

**Category:** [Platform](https://discourse.openehr.org/c/platform-implem/7)
**Created:** 2024-10-24 12:34 UTC
**Views:** 146
**Replies:** 3
**URL:** https://discourse.openehr.org/t/aql-on-folder/5854

---

## Post #1 by @stefanspiska

The latest develop of ehrbase (PR https://github.com/ehrbase/ehrbase/pull/1401) now supports AQL on folder (which are in the directory of the ehr).

Thus Queries of the form 
```
SELECT 
  e/ehr_id/value,
  f/uid/value,
  f/name/value,
  c/uid/value 
FROM EHR e 
    CONTAINS FOLDER f[openEHR-EHR-FOLDER.generic.v1] 
      CONTAINS COMPOSITION c
```

now work.

Note since this is an experimental feature this is beyond a feature flag : 
```
ehrbase:
  rest:
    experimental:
      aql-on-folder:
        enabled: true
```
```
EHRBASE_REST_EXPERIMENTAL_AQLONFOLDER_ENABLED=true
```

For the full set of capabilities see https://github.com/ehrbase/conformance-testing-documentation/blob/main/FOLDER.md

---

## Post #2 by @sebastian.iancu

This is a nice addition! I would suggest promoting it to an AQL jira ticket, so that we can consider standardizing it (work for AQL WG).
Further question(s):
- can you have nested  contains folder ?
- Can you also use the name of the folder as predicate?
- Can you retrieve as column folder data (from other details)?
- Have you already adjusted the aql grammar for this?

---

## Post #3 by @vidi42

Small correction, it's now `ehrbase.aql.experimental.aql-on-folder.enabled`.
```
ehrbase:
  aql:
    experimental:
      aql-on-folder:
        enabled: true
```

```
EHRBASE_AQL_EXPERIMENTAL_AQLONFOLDER_ENABLED=true
```

@sebastian.iancu the conformance testing maybe answers some of your questions


[quote="sebastian.iancu, post:2, topic:5854"]
* can you have nested contains folder ? -> [Find by](https://github.com/ehrbase/conformance-testing-documentation/blob/main/FOLDER.md#find-by) and [Folder in folder](https://github.com/ehrbase/conformance-testing-documentation/blob/main/FOLDER.md#folder-in-folder)
* Can you also use the name of the folder as predicate? -> [Find by](https://github.com/ehrbase/conformance-testing-documentation/blob/main/FOLDER.md#find-by)
* Can you retrieve as column folder data (from other details)? -> [Select paths in folder](https://github.com/ehrbase/conformance-testing-documentation/blob/main/FOLDER.md#select-paths-in-folder-1)
* Have you already adjusted the aql grammar for this? -> I'll let @stefanspiska speak on this one and maybe other points
[/quote]

---

## Post #4 by @stefanspiska

@sebastian.iancu 

The AQL grammar doesn't restrict which  Subclasses of Locatable is usable in FROM and CONTAINS and thus FOLDER is already covered from a grammar standpoint.

So you can select any  json attribute via its AQL path including  details and use it in SELECT,WHERE or ORDER BY

For the contains logic it works the same way as `CLUSTER contains CLUSTER`
So `FROM EHR CONTAINS FOLDER f1[openEHR-EHR-FOLDER.generic.v1,'root1'] CONTAINS FOLDER f2 [openEHR-EHR-FOLDER.episode_of_care.v1,'subsubfolder1']` is defined and means that  in the  (JSON) Tree starting from` EHR. directory` there exist  
node with

```
_type = FOLDER
name/value =  'root1'
archetype_node_id = 'openEHR-EHR-FOLDER.generic.v1'
```
and
in the  (JSON) sub-tree starting from the above node contains a node with

```
_type = FOLDER
name/value =  'subsubfolder1'
archetype_node_id = 'openEHR-EHR-FOLDER.episode_of_care.v1'
```

Also like EHR and EHR_STATUS we treat  certain [OBJECT_REF](https://specifications.openehr.org/releases/BASE/latest/base_types.html#_object_ref_class)  as being the Object itself.

So `FOLDER.items` is treated as contains the COMPOSITIONS and not just Object refs concerning CONTAINS

Thus `FROM EHR CONTAINS FOLDER f1[openEHR-EHR-FOLDER.generic.v1,'root1'] CONTAINS COMPOSITION CONTAINS OBSERVATION [openEHR-EHR-OBSERVATION.blood_pressure.v1]` 

 means that  in the  (JSON) Tree starting from` EHR. directory` there exist  
node with

```
_type = FOLDER
name/value =  'root1'
archetype_node_id = 'openEHR-EHR-FOLDER.generic.v1'
```
and
the (JSON) sub-tree stating von the node above contains
(after replacing FOLDER.items with the corresponding compositions)  a node of` type=COMPOSITION`
and the  (JSON) subtree starting von this node contains a Node with
```
type=OBSERVATION
archetype_node_id = openEHR-EHR-OBSERVATION.blood_pressure.v1
```
Note that an OBSERVATION is always contained in a COMPOSITION so we could have written the above as

`  FROM EHR CONTAINS FOLDER  f1[openEHR-EHR-FOLDER.generic.v1,'root1'] CONTAINS OBSERVATION [openEHR-EHR-OBSERVATION.blood_pressure.v1]`

---


**Canonical:** https://discourse.openehr.org/t/aql-on-folder/5854
**Original content:** https://discourse.openehr.org/t/aql-on-folder/5854