2017-11-22 - SLPG Meeting

Linda, all

Looking through the examples I wonder if we should consider some of the following:

1. much more explicit reference to the RF2 tables and attributes when adding {{  }}  filters. No doubt this would come at the expense of 'human-readability' in all but the most trivial cases, but given the goal of  '...query[ing] over SNOMED CT RF2 content...' perhaps human-readability was always going to be a casualty of the query language. I have in mind, in particular, the examples where Concepts are followed by a filter that makes reference to properties of terms/descriptions - an inevitably further entries in language reference sets (and, see below, relationships). Whilst there is probably a predictable shorthand that would avoid table names ('a term filter on a concept necessarily refers to 'Descriptions.term WHERE Descriptions.ConceptId=Concepts.Id' etc.), it strikes me as preferable to agree a notation (for use inside filter braces) that ties each filter property to a table (even if it still leaves the exact nature of the 'join' implicit). This could be the prefixes already offered, or (if required) could perhaps be a compromise of shorter aliasies (e.g. << 64572001 |Disease| {{ D.term = “*heart*”, D.typeId =  synonym, D.active=0, C.module=core, D.module='UKClinicalModule' }} - any active core disease concepts where there's a (now inactive) UK description matching “*heart*”). Not quite sure how this would extend to various refsets, but there's probably a scalable shorthand (like C, D, R for the various types/patterns, and then tightening up on each actual refset by reference to its refsetId etc). The alternative 'implicit until there's a chance of ambiguity' approach seems too loose. In particular, << 246061005 | Attribute | {{ active=0 }} (after an attribute name - see below) would probably mostly be used to refer to the status of the relationship, but would be indistinguishable from the same filter looking only for attributes where the name is now inactive. 

2. We touched on 'where' each filter should go last time. looking at SPARQL and CYPHER (I'm sure other participants know of other relevant examples), I wonder if the following would work:

   1. A 'global' header/prefix filter that 'comes first' declares the substrate, any global cross-table filters, and maybe any tokens/shorthands used later.

   2. Any subsequent filter only has the scope of the resolved set of concepts immediately preceding it, unless the scope is 'extended' by parentheses (like the examples on slide 23)

   3. Relationship filters would sit:

      1. after the attribute Name for role relationships - thus being filters that could contain C, D, Refset and R filter prefixes

      2. for hierarchical relationships the filter would sit after each constraint operator (e.g. << {{ R.module=UKExtension, R.active=0 }} 64572001 |Disease|. I accept this probably needs further unpacking. I recall you said there were problems with this approach, but I wonder if this is more a consequence the constraint operator 'shorthand', and where a more verbose query representation of ancestry/descendants (as used in e.g. CYPHER) would be isomorphic with role relationships.

Ed

Dear All,

I support Ed's no 1 in that I also would prefer explicit references to tables rather than having to assume (or rely on defaults) which component the effectiveTime applies to.

I also have another issue about having multiple filters applying to components of the same kind. When querying translations it is sometimes needed to query for EN terms and SV terms simultaneously to check consistency of translations. E.g. concepts with EN terms "*substance*" and SE terms "*medel*" (I actually needed to do exactly this yesterday!). Do we allow stacking of filters, like * {{ languageCode = EN, term = "*substance*" }} {{  languageCode = SV, term = "*medel*" }}. The formal semantics of filters need to be defined as well, here I have asumed that filters evaulate to sets of concept IDs and that filter application evaluate to the intersection of the filter set and the preceding result set, either a result of ECL evaluation or another filter.

/Daniel

Thanks for your thoughts on this Ed and Daniel. Here are some ramblings in response:

• I'd be happy for the substrate and/or other bits and pieces we need to be declared at the start of the query... however, I remember we had a use case where we wanted to compare 2 snapshot versions - for example '20170731 international edition' MINUS '20170131 international edition', to find the concepts that were in the 20170731 version that were not in 20170131 version. To support use cases such as this (and many other examples) I think we need to allow nesting of queries (with filters) inside other queries. This may not lend itself to defining the substrate in a single header ... just something to think about.

• I like the suggestion to make the filters more explicit in terms of the table they refer to. And I like the shorter form of these filter prefixes that Ed has suggested - namely C for Concept, D for Description, R for Relationship  ... and I would suggest adding M for Refset Member. We will have to modify the examples to check that this always works as we would expect.

• The example that Ed gave of "<< 64572001 |Disease| {{ D.term = “*heart*”, D.typeId =  synonym, D.active=0, C.module=core, D.module='UKClinicalModule' }}" raised some interesting points, including Daniel's suggestion of how we filter on multiple descriptions. It would be great if we can get really clear semantics on exactly what each filter means, and how the filter should be evaluated. I don't think it's clear from this example that the description being constrained is necessarily the same description. The letter "D" appears 4 times in the example filter - so are those 4 filters referring to the same Description, or 4 different Descriptions? We can assert that if it is in the same filter, then it must refer to the same Description ... but visually I don't think this is necessarily clear. So, with this in mind, what does the group think about this idea (... just brainstorming here):

  • We could 'type' the filters, using the C:, D:, R:, and M: labels to indicate what type of component the subsequent criteria in the filter applies to (e.g. {{ D:  term = "*heart*" }}

    • Each C type of filter could be used 0..1 times, and would mean "The concept itself has the following properties" 

      • i.e. For each concept C in the result set, C has the following concept properties

    • Each D type of filter could be used 0..* times, and would mean "There exists a description on the given concept, which has the following properties"

      • i.e. For each concept C in the result set, there exists a description D with D.conceptId = C.id plus the listed description properties

    • Each M type of filter could be used 0..* times, and would mean "There exists a refset member whose referenced component is the given concept OR whose referenced component is a description on the given concept, which has the following properties"
      
      

      • ie. For each concept C in the result set, there exists a refset member M with M.referencedComponentId = C.id and the listed refset member properties

  • We could treat each filter as applying to a (possibly) different component (e.g. "There exists a description X for which criteria 1 is true" and "There exists a description Y for which criteria 2 is true" logic)

    • Question: Do we need a way of saying that the 2 descriptions must necessarily be separate/different descriptions?

  • We could treat each criteria within a single filter as applying to a single component (e.g. "There exists a description X for which criteria 1 is true AND criteria 2 is true").

  • So, for example, Ed's query would look like - Options (for discussion)
    
    

    • << 64572001 |Disease| {{ C: module = core }} {{ D: term = “*heart*”, typeId =  synonym, active=0, module='UKClinicalModule' }}
      
      

    • << 64572001 |Disease| {{ C.module = core ,  D.term = “*heart*”, D.typeId =  synonym, D.active=0, D.module='UKClinicalModule' }}
      
      

    • ... which would logically mean "Descendants or self of |Disease|, where the concept has module = |core|, and where there exists a description on this concept, which has term = "*heart*", typeId = synonym, active = 0 and module = |UKClinicalModule|.

  • This would also allow Daniel's query to be defined like - Options (for discussion)

    • << 64572001 |Disease| {{ D: term = “*substance*”, languageCode = "EN"}}, {{ D: term = "*medel*", languageCode = "SE" }}

    • << 64572001 |Disease| {{ D1: term = “*substance*”, languageCode = "EN"}}, {{ D2: term = "*medel*", languageCode = "SE" }}
      
      

    • << 64572001 |Disease| {{ D1.term = “*substance*”, D2.languageCode = "EN",  D2.term = "*medel*", D1.languageCode = "SE", D1.term = D2.term, D1.id != D2.id }}
      
      

      • ... which would logically mean "Descendants or self of |Disease|, where there exists a description on this concept, which has term = "*substance*" and languageCode = "EN", AND where there also exists a description, which has the term = "*medel*" and languageCode = "*SE*" }}
        
        

• With respect to Relationship filters, I think I agree with Ed ... but let's follow through the discussion a bit more:

  • We could (note that I am NOT recommending this) allow relationship filters to apply to the results of a query (as per the Concept and Description filters), such that C.id = R.sourceId. For example:
    
    

    • << 64572001 |Disease| {{ C: definitionStatus = primitive }} {{ R: characteristicType = stated, typeId != 116680003 }}

      • Which would mean "Descendants or self of |Disease|, where the concept has a definitionStatus = primitive, and where there exists a relationship on this concept (ie C.id = R.sourceId) where characteristicType = stated and typeId NOT = |is a|."
        
        

  • However, what if we wanted to join on R.destinationId instead? Perhaps (and I'm NOT recommending this) we would need to specify the different join condition like:
    
    

    • << 64572001 |Disease| {{ C: definitionStatus = primitive }} {{ R [destinationId]: characteristicType = stated, typeId != 116680003 }}

      • Which could (?) mean "Descendants or self of |Disease|, where the concept has a definitionStatus = primitive, and where there exists a relationship where C.id = R.destinationId and R.characteristicType = stated and R.typeId NOT = |is a|."

  • If instead we adopt Ed's suggestion of applying the relationship filter to a refinement (e.g. by placing it directly after the attribute name) - we could say:

    • << 64572001 |Disease| : 363698007 |Finding site| {{ R: characteristicType = stated }} = << 39607008 |Lung structure|
      
      

      • ... which would mean "The descendants or self of |Disease|, C, where there exists a relationship where C.id = R.sourceId and R.typeId = |Finding site| and R.characteristicType = stated.

    • I think this makes sense (ie to join on both sourceId and typeId). My only concern with this approach is that IF we allow the Relationship filter to be applied here, as well as the more general case above (where R.typeId is not restricted), that this example might be confused with the similar query:

      • << 64572001 |Disease| : (363698007 |Finding site| {{ R: characteristicType = stated }}) = << 39607008 |Lung structure|
        
        

        • Which may mean that the attribute concept A used in the refinement is in the set {|Finding site|} where there exists a relationship for which A.id = R.sourceId and R.characteristicType = stated .... or in other words, the relationship filter applies to the relationships on the concept |Finding site|, rather than applying to the relationships on |Disease| which have a type of |Finding site|.

  •  But, if we agree to ONLY do it Ed's way .... that is a relationship filter must ALWAYS be placed after an attribute A, and the join condition is ALWAYS that C.id =R.sourceId AND R.typeId = A, THEN any ambiguity probably goes away.

  • Please note, that it would be nice if the reverse flag "R" changed the join condition to being C.id = R.destinationId AND R.typeId = A. For example:

    • << 91723000 |Anatomical structure| : R 363698007 |Finding site| {{ R: characteristicType = stated }}) = << 125605004 |Fracture of bone|
      
      

      • ... would/could mean "The descendants of anatomical structure that are the finding site of a descendant or self of fracture of bone, where the finding site relationship has characteristicType = stated"

  • If we now consider applying relationship filters to constraint functions "<", "<<", ">", ">>" then the logic is still not completely clear to me:
    
    

    • "< X", "<< X", "<! X"  would imply a join condition of C.id = R.sourceId, R.typeId = |is a|. However, does this also imply that R.destinationId = X? For example:
      
      

      • << {{ R: module=UKExtension, active=0 }} 64572001 |Disease|

        • Does this mean:

          • (a) The descendants or self of |Disease| which have an |is a| relationship to |Disease|, where this |is a| relationship is inactive and in the UKExtension module.

          • (b) The descendants or self of |Disease| which have an |is a| relationship to a concept that is a descendant of |Disease|, where this |is a| relationship is inactive and in the UKExtension module

          • (c) The descendants or self of |Disease| which have an |is a| relationship to any concept , where that |is a| relationship is inactive and in the UKExtension module

    • Similarly, the interpretation of a relationship filter on "> X" ">> X", ">! X"  would need to be defined consistently.

Apologies for the long reply, but thanks again for your thoughts ... we can discuss this further at this week's SLPG meeting.