concept lookup rule implementation

[sshenzha]

In OMOP, you don't query clinical data by string. The intended pattern is always:

WHERE drug_concept_id IN (
    SELECT concept_id FROM concept WHERE concept_code = '308136' AND vocabulary_id = 'RxNorm'
)

and not

SELECT * FROM drug_exposure de
JOIN concept c ON de.drug_concept_id = c.concept_id
WHERE c.concept_name LIKE '%aspirin%'

Although the second query works, it's structurally wrong. The concept_name LIKE filter performs concept identification within a clinical query rather than in a dedicated concept_id lookup.

[sshenzha]

The rule identifies all the columns where a string comparison could be used to "identify" a concept

Table	Columns watched
concept	concept_name, concept_code, vocabulary_id, domain_id, concept_class_id
concept_synonym	concept_synonym_name
concept_ancestor	min_levels_of_separation, max_levels_of_separation
vocabulary	vocabulary_name, vocabulary_reference, vocabulary_version
domain	domain_name
concept_class	concept_class_name
relationship	relationship_name, is_hierarchical, defines_ancestry

Then, walks through the AST checking in any of the aforementioned tables.columns are present.
For each of the detected patterns, it checks if this SELECT is actually doing a concept_id lookup, or if it's something else.

• Condition A [Is the SELECT reading from a vocabulary table?]: It checks both the FROM clause and any JOINs for one of the known vocabulary tables (concept, concept_synonym, concept_ancestor, concept_relationship, vocabulary, domain, concept_class, relationship). If none of these are present, it's not a lookup context -> fail immediately.
• Condition B [Is this an EXISTS subquery that correlates on concept_id?]: EXISTS doesn't project columns, so you can't check the SELECT list. Instead, it looks for a correlation pattern in the WHERE: EXISTS (SELECT 1 FROM concept c WHERE c.concept_id = co.condition_concept_id AND c.standard_concept = 'S'). It specifically looks for an equality between concept_id and any *_concept_id column. If found, this counts as a valid lookup context.
• Condition C [Does the SELECT output a concept_id?]: It scans the SELECT projection list for any of these:

  Pattern	Example	Why it counts
  SELECT *	SELECT * FROM concept WHERE ...	Could include concept_id
  concept_id directly	SELECT concept_id FROM concept	The canonical lookup
  Aliased to *_concept_id	SELECT concept_id AS drug_concept_id	Renamed but still a lookup
  concept_id_1 / concept_id_2	From concept_relationship	Relationship lookups
  descendant_concept_id / ancestor_concept_id	From concept_ancestor	Hierarchy lookups

Examples:

-- PASS: SELECT outputs concept_id, reading from concept table
SELECT concept_id FROM concept WHERE concept_name LIKE '%diabetes%'

-- PASS: EXISTS with concept_id correlation
WHERE EXISTS (SELECT 1 FROM concept c WHERE c.concept_id = co.condition_concept_id AND c.concept_name = 'Diabetes')

-- PASS: CTE that outputs concept_id
WITH mapped AS (SELECT concept_id FROM concept WHERE concept_code = '308136' AND vocabulary_id = 'RxNorm')
SELECT * FROM drug_exposure WHERE drug_concept_id IN (SELECT concept_id FROM mapped)

-- FAIL: concept_name filter in a query that outputs clinical columns, not concept_id
SELECT de.person_id FROM drug_exposure de
JOIN concept c ON de.drug_concept_id = c.concept_id
WHERE c.concept_name LIKE '%aspirin%'

-- FAIL: vocabulary_id filter in a SELECT that doesn't output concept_id
SELECT concept_name FROM concept WHERE vocabulary_id = 'RxNorm'