Handle medications with missing dm+d codes

[inglesp]

Some entries in TPP's MedicationDictionary table have nulls in the dmd_id column. In most cases, this is not a problem because the entries missing a dm+d code do not map to anything in dm+d.

However, in a handful of cases an entry missing a dm+d code does map to something in dm+d, and so the dmd_id column is incorrectly null. The underlying data is unlikely to be fixed, and so we need to provide a way for members of our clinical informatics team to provide mappings from a Multilex code to dm+d code.

Then, every time we currently query the MedicationDictionary table, we should instead query the union of this table with any extra data from our own Multilex to dm+d mapping.

[evansd]

This sounds sensible (if annoying).

On the ehrQL side of things, I think this can all be handled in the medications QueryTable definition, as you suggest. It's just a case of LEFT JOINing our manually curated table alongside MedicationDictionary and then using COALESCE to combine IDs from the two tables, using whichever is non-null:
https://github.com/opensafely-core/databuilder/blob/a238c691c0d343ecca49cc9ce968736d8188e310/databuilder/backends/tpp.py#L95-L102

Something like:

SELECT
    meds.Patient_ID AS patient_id,
    CAST(meds.ConsultationDate AS date) AS date,
    COALESCE(our_dict.DMD_ID, dict.DMD_ID) AS dmd_code,
    meds.MultilexDrug_ID AS multilex_code
FROM MedicationIssue AS meds
LEFT JOIN MedicationDictionary AS dict
ON meds.MultilexDrug_ID = dict.MultilexDrug_ID
LEFT JOIN OurCustomDictionary AS our_dict
ON meds.MultilexDrug_ID = our_dict.MultilexDrug_ID

It's possible that it will be a fairly non-invasive change to Cohort Extractor as well, although I wouldn't want to bet against something unexpected biting us. The relevant bit of code is:

cohort-extractor/cohortextractor/tpp_backend.py

Lines 1089 to 1098 in aa2564a

	return self._patients_with_events(
	"MedicationIssue",
	"""
	INNER JOIN MedicationDictionary
	ON MedicationIssue.MultilexDrug_ID = MedicationDictionary.MultilexDrug_ID
	""",
	"DMD_ID",
	codes_are_case_sensitive=False,
	**kwargs,
	)

So possibly something like this would work:

return self._patients_with_events(
    "MedicationIssue",
    """
    LEFT JOIN MedicationDictionary
    ON MedicationIssue.MultilexDrug_ID = MedicationDictionary.MultilexDrug_ID
    LEFT JOIN OurCustomDictionary
    ON MedicationIssue.MultilexDrug_ID = OurCustomDictionary.MultilexDrug_ID
    """,
    "COALESCE(OurCustomDictionary.DMD_ID, MedicationDictionary.DMD_ID)",
    codes_are_case_sensitive=False,
    **kwargs,
)