In that case. we have a shared understanding. I don’t think I am diverging meaningfully from what you said.
- the FROM states the target object structures
- the WHERE indicates which rows to keep
- and the SELECT says which columns from the remaining set of rows (= reduced table/view) to give me back
All of that makes perfect sense but it still leaves out what for me is the critical breakthrough in my understanding…
Once you start nesting CONTAINS, the target object is actually the combination of all the structures within the CONTAINS tree, with a focus on the most granular object.
so CONTAINS COMPOSITION CONTAINS OBSERVATION means
give me all the OBSERVATIONS (each within its own parent COMPOSITION)
and not give me all the COMPOSITIONS which contain OBSERVATIONS (which is the ‘natural’ meaning, I suspect ro most people- and I think the source of most of the confusion (certainly mine).
I am asking for each individual OBSERVATION to be given a row of its own, in the context of its partent COMPOSITION.
Now that I understand this is how things work, that is quite useful because it may actually be that I want to tease out each individual observation , along with e.g a composiitonID.
SELECT c/uid , o FROM EHR e CONTAINS COMPOSITION C CONTAINS OBSERVATION o
though in general I will just want the a single composition, which is where Seref’s suggestion for EXISTS would work - is there a reason why a simple EXISTS might not be enough - it is important that the EXISTS clause accepts an ALIAS so that it can be used in WHERE clauses
SELECT c/uid , o FROM EHR e CONTAINS COMPOSITION EXISTS OBSERVATION o WHERE o/origin/value >= “2020-03-04”
So @Pablo, I don’t think there is actually a ‘technical view’ and a ‘non-technical view’ (as I also used to argue). The way that CONTAINS works is non-intuitive to a non-CDR developer audience but it is relatively easy to explain, once understood. Introducing something like EXISTS solves the problem for me (or at least part of it).
AFAIK all of the CDR developers have adopted the same approach to CONTAINS so the last thing I want to do is disrupt that, especially if it is correct from a comp science perspective.
I think we have solution to this part of the permutation problem.
Do we need
EXISTS(COMPOSITION C contains OBSERVATION o)