inspectomop.queries.condition.condition_concepts_for_keyword

inspectomop.queries.condition.condition_concepts_for_keyword(keyword, inspector, return_columns=None)

Retrieves standard concepts for a condition/keyword.

Parameters:

• keyword (str)

• inspector (inspectomop.inspector.Inspector)

• return_columns (list of str, optional) –

  • optional subset of columns to return from the query

  • columns : [‘concept_id’, ‘concept_name’, ‘concept_code’, ‘concept_class_id’, ‘vocabulary_id’]

Returns:

results

Return type:

sqlalchemy.sql.expression.Executable

Notes

Original SQL

C02: Find a condition by keyword:

SELECT
    T.Entity_Concept_Id,
    T.Entity_Name,
    T.Entity_Code,
    T.Entity_Type,
    T.Entity_concept_class,
    T.Entity_vocabulary_id,
    T.Entity_vocabulary_name
FROM (
    SELECT
        C.concept_id Entity_Concept_Id,
        C.concept_name Entity_Name,
        C.CONCEPT_CODE Entity_Code,
        'Concept' Entity_Type,
        C.concept_class_id Entity_concept_class,
        C.vocabulary_id Entity_vocabulary_id,
        V.vocabulary_name Entity_vocabulary_name,
        NULL Entity_Mapping_Type,
        C.valid_start_date,
        C.valid_end_date
    FROM concept C
    JOIN vocabulary V ON C.vocabulary_id = V.vocabulary_id
    LEFT JOIN concept_synonym S ON C.concept_id = S.concept_id
        WHERE
            (C.vocabulary_id IN ('SNOMED', 'MedDRA') OR LOWER(C.concept_class_id) = 'clinical finding' ) AND
            C.concept_class_id IS NOT NULL AND
            ( LOWER(C.concept_name) like '%myocardial infarction%' OR
            LOWER(S.concept_synonym_name) like '%myocardial infarction%' )
    ) T
WHERE sysdate BETWEEN valid_start_date AND valid_end_date
ORDER BY 6,2;