HERMES Reproducing SQL queries for related concepts

Jorge · April 21, 2015, 5:16pm

The HERMES project is great and provides some real insight into how concepts work in the ODM. However I have not been able to completely replicate the related concepts screen. Can you provide some basic queries on how to find all of a particular concepts relatives?

Frank · April 21, 2015, 6:00pm

Since all Hermes is doing is exposing WebAPI functionality you can see the source code behind the queries in the WebAPI project. Hermes calls the WebAPI which uses the following query to get the complete list of related concepts:

github.com

OHDSI/WebAPI/blob/master/src/main/resources/resources/vocabulary/sql/getRelatedConcepts.sql

select distinct * from (
    select c.CONCEPT_ID, CONCEPT_NAME, ISNULL(STANDARD_CONCEPT,'N') STANDARD_CONCEPT, ISNULL(c.INVALID_REASON,'V') INVALID_REASON, CONCEPT_CODE, CONCEPT_CLASS_ID, DOMAIN_ID, c.VOCABULARY_ID, RELATIONSHIP_NAME, 1 RELATIONSHIP_DISTANCE
    from @CDM_schema.CONCEPT_RELATIONSHIP cr 
    join @CDM_schema.CONCEPT c on cr.CONCEPT_ID_2 = c.CONCEPT_ID 
    join @CDM_schema.RELATIONSHIP r on cr.RELATIONSHIP_ID = r.RELATIONSHIP_ID 
    where cr.CONCEPT_ID_1 = @id and cr.INVALID_REASON IS NULL
    union 
    select ANCESTOR_CONCEPT_ID, CONCEPT_NAME, ISNULL(STANDARD_CONCEPT,'N') STANDARD_CONCEPT, ISNULL(c.INVALID_REASON,'V') INVALID_REASON, CONCEPT_CODE, CONCEPT_CLASS_ID, DOMAIN_ID, c.VOCABULARY_ID, 'Has ancestor of' , MIN_LEVELS_OF_SEPARATION RELATIONSHIP_DISTANCE  
    from @CDM_schema.CONCEPT_ANCESTOR ca 
    join @CDM_schema.CONCEPT c on c.CONCEPT_ID = ca.ANCESTOR_CONCEPT_ID 
    where DESCENDANT_CONCEPT_ID = @id
    and ANCESTOR_CONCEPT_ID <> @id
    union 
    select DESCENDANT_CONCEPT_ID, CONCEPT_NAME, ISNULL(STANDARD_CONCEPT,'N') STANDARD_CONCEPT, ISNULL(c.INVALID_REASON,'V') INVALID_REASON, CONCEPT_CODE, CONCEPT_CLASS_ID, DOMAIN_ID, c.VOCABULARY_ID, 'Has descendant of' , MIN_LEVELS_OF_SEPARATION RELATIONSHIP_DISTANCE  
    from @CDM_schema.CONCEPT_ANCESTOR ca 
    join @CDM_schema.CONCEPT c on c.CONCEPT_ID = ca.DESCENDANT_CONCEPT_ID 
    where ANCESTOR_CONCEPT_ID = @id
    and DESCENDANT_CONCEPT_ID <> @id
    union
    select distinct c3.CONCEPT_ID, c3.CONCEPT_NAME,isnull(c3.standard_concept,'N') STANDARD_CONCEPT, ISNULL(c3.INVALID_REASON,'V') INVALID_REASON, c3.CONCEPT_CODE, c3.CONCEPT_CLASS_ID, c3.DOMAIN_ID, c3.VOCABULARY_ID, CONCAT('Has relation to descendant of : ', RELATIONSHIP_NAME) RELATIONSHIP_NAME, min_levels_of_separation RELATIONSHIP_DISTANCE

This file has been truncated. show original

Jorge · April 21, 2015, 6:48pm

Thanks a lot Frank. This was very helpful.