Thanks for your respose Greg. I'm not really concerned about the details of specific archetypes - I just used the ubiquitous blood pressure one because that's the one used in a lot of the example documentation.
My question is more about the how AQL should handle querying data that conforms to archetypes that contain one or more one-to-many relationships. John > -----Original Message----- > From: openehr-technical-bounces at openehr.org > [mailto:openehr-technical-bounces at openehr.org] On Behalf Of > Greg Caulton > Sent: Wednesday, 25 February 2009 2:58 AM > To: openehr-technical at openehr.org > Subject: Re: AQL queries and one-many relationships > > My assumption was that the 'contains > openEHR-EHR-COMPOSITION.encounter.v1' will restrict the result set to > be in this case blood pressure observations that were recorded on an > encounter note (or any document identified as such). > > If I make up an archetype called assessment you could have the > following to get blood pressure results from two different document > types - not sure if they intention was for all assessments to be > designated as encounter notes though - perhaps. > > SELECT o > FROM > EHR > CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > CONTAINS OBSERVATION o > [openEHR-EHR-OBSERVATION.blood_pressure.v1] AND > CONTAINS COMPOSITION a [openEHR-EHR-COMPOSITION.assessment.v1] > CONTAINS OBSERVATION o2 > [openEHR-EHR-OBSERVATION.blood_pressure.v1] > > > Greg > > http://www.patientos.org > > On Tue, Feb 24, 2009 at 7:00 AM, > <openehr-technical-request at openehr.org> wrote: > > Send openEHR-technical mailing list submissions to > > ? ? ? ?openehr-technical at openehr.org > > > > To subscribe or unsubscribe via the World Wide Web, visit > > ? ? ? ? > http://lists.chime.ucl.ac.uk/mailman/listinfo/openehr-technical > > or, via email, send a message with subject or body 'help' to > > ? ? ? ?openehr-technical-request at openehr.org > > > > You can reach the person managing the list at > > ? ? ? ?openehr-technical-owner at openehr.org > > > > When replying, please edit your Subject line so it is more specific > > than "Re: Contents of openEHR-technical digest..." > > > > > > Today's Topics: > > > > ? 1. AQL queries and one-many relationships > (John.Ryan-Brown at csiro.au) > > ? 2. OpenEHR'ED (Derek Meyer) > > > > > > > ---------------------------------------------------------------------- > > > > Message: 1 > > Date: Tue, 24 Feb 2009 17:23:00 +1100 > > From: <John.Ryan-Brown at csiro.au> > > Subject: AQL queries and one-many relationships > > To: <openehr-technical at openehr.org> > > Message-ID: > > ? ? ? ? > <8C3F2174B3FE2B408CB380513186BEC45752819AE6 at EXNSW-MBX03.nexus. > csiro.au> > > > > Content-Type: text/plain; charset="us-ascii" > > > > Hello. I am currently investigating AQL and would like to > know how the following queries would be answered by a > conformant AQL query engine. I understand that the structure > of AQL query results are not standardized yet in the AQL > specifications, so if the spec cannot give a definitive > answer what would Ocean's AQL query engine implementation do? > > > > 1. ?SELECT > > ? ? ? > o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value > AS PosturalChangeSystolic, > > ? ? ? > o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value > AS ParadoxSystolic > > ? ?FROM > > ? ? ?EHR > > ? ? ?CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > > ? ? ?CONTAINS OBSERVATION o > [openEHR-EHR-OBSERVATION.blood_pressure.v1] > > ? ?WHERE > > ? ? ? > o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value > /value >= 140 OR > > ? ? ? > o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value > /value >= 140 > > > > This query attempts to find all Systolic readings for > Paradox and Postural Change blood pressure events where the > Systolic reading for either is >= 140. As there is a > one-to-many relationship between a blood pressure observation > and both Paradox and Postural Change events, how should the > query be processed? If an observation has three Postural > Change events where Systolic >= 140, and four such Paradox > events, would the query return twelve rows (using my > relational database thinking). Or would one row be returned, > having two "lists" (with three and four members respectively) > of Systolic readings? > > > > > > 2. ?SELECT o > > ? ?FROM > > ? ? ?EHR > > ? ? ?CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > > ? ? ?CONTAINS OBSERVATION o > [openEHR-EHR-OBSERVATION.blood_pressure.v1] > > ? ?WHERE > > ? ? ? > o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value > /value >= 140 OR > > ? ? ? > o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value > /value >= 140 > > > > This query is the same as the previous one, except that it > returns the whole observation. It seems to me that all > readings should be returned, regardless of their systolic values? > > > > > > 3. ?SELECT o > > ? ?FROM > > ? ? ?EHR > > ? ? ?CONTAINS COMPOSITION c [openEHR-EHR-COMPOSITION.encounter.v1] > > ? ? ?CONTAINS OBSERVATION o > [openEHR-EHR-OBSERVATION.blood_pressure.v1] > > ? ?WHERE > > ? ? ? > o/data[at0001]/events[at0031]/data[at0003]/items[at0004]/value > /value >= 140 AND > > ? ? ? > o/data[at0001]/events[at1004]/data[at0003]/items[at0004]/value > /value >= 140 > > > > This query is the same as the previous one, except that the > OR has been changed to an AND. It could be argued that only > the Paradox and Postural Change events with a Systolic > reading >= 140 should be returned, but it could also be > argued that all readings should be returned, as the whole > observation has been selected. > > > > > > The problem I have is how to treat queries which have in > the WHERE clause a path expression that traverses through a > 1:n relationship. In trying to think through the semantics of > such queries, I come up with ambiguities. In a relational > query (i.e. SQL) the "equivalent" path expression would have > to be expressed as a join between tables in the FROM clause, > thus removing the ambiguities. > > > > Am I missing something or are my concerns relevant? If so, > how does the spec/Ocean implementation address them? > > > > Thanks, > > > > John Ryan-Brown > > The Australian e-Health Research Centre > > CSIRO ICT Centre > > Brisbane > > > > _______________________________________________ > openEHR-technical mailing list > openEHR-technical at openehr.org > http://lists.chime.ucl.ac.uk/mailman/listinfo/openehr-technical >
