@ericaVoss and @Christian_Reich,
This topic is so we can decide how to best “roll up” in cases where we want to see evidence for a drug-HOI pair but some evidence sources use ingredient ids while and others use clinical drug ids.
I made several small changes to the code that loads the evidence base tables for LAERTES and am pleased to say that I now see evidence across sources where expected so long as the drugs are at the same level. I have loaded sample data from PubMed, SPLICER, EU SPC, and PV signals that you can view for yourself and explore. To make the conversation more concrete, please try these queries out on the dev database (using pgadmin3) and examine the results:
SELECT DV1.DRUG AS INGREDIENT_ID, DV1.RXNORM_DRUG AS INGREDIENT, DV2.DRUG AS CLINICAL_DRUG_ID, DV2.RXNORM_DRUG AS CLINICAL_DRUG, DV1.HOI, DV1.SNOMED_HOI, DV1.AERS_EB05, DV1.AERS_EBGM, DV1.AERS_REPORT_COUNT, DV2.SPL_SPLICER_COUNT, DV2.SPL_SPLICER_LINK FROM DRUG_HOI_EVIDENCE_VIEW DV1, DRUG_HOI_EVIDENCE_VIEW DV2, CONCEPT_ANCESTOR CA WHERE DV1.HOI = DV2.HOI AND DV1.AERS_EB05 IS NOT NULL AND DV2.SPL_SPLICER_COUNT IS NOT NULL AND DV1.DRUG = CA.ANCESTOR_CONCEPT_ID AND DV2.DRUG = CA.DESCENDANT_CONCEPT_ID ORDER BY HOI LIMIT 100;
This query brings together PV signal data with SPL ADRs. The counts for aers reports indicates number of spontaneous reports while the counts for SPLICER indicate the number of product labels for the specific clinical drug. The DV2.SPL_SPLICER_LINK column holds a link that the API will be able to use to get back the text from the source documents where the evidence was found. Notice that total counts are not necessarily informative when considering SPL evidence for clinical drugs that differ only by dose. It might be just useful to assign a count of ‘1’ to the “rolled up” count for the case that there is ANY SPL evidence for the drug-HOI and then provide the key to rows in the pivot table where that evidence exists.
SELECT DV1.DRUG AS INGREDIENT_ID, DV1.RXNORM_DRUG AS INGREDIENT, DV2.DRUG AS CLINICAL_DRUG_ID, DV2.RXNORM_DRUG AS CLINICAL_DRUG, DV1.HOI, DV1.SNOMED_HOI, DV1.medline_mesh_clin_trial_count, DV1.medline_mesh_clin_trial_link, DV1.medline_mesh_case_report_count, DV1.medline_mesh_case_report_link, DV1.medline_mesh_other_count, DV1.medline_mesh_other_link, DV2.SPL_SPLICER_COUNT, DV2.SPL_SPLICER_LINK FROM DRUG_HOI_EVIDENCE_VIEW DV1, DRUG_HOI_EVIDENCE_VIEW DV2, CONCEPT_ANCESTOR CA WHERE DV1.HOI = DV2.HOI AND (DV1.medline_mesh_clin_trial_count IS NOT NULL OR DV1.medline_mesh_case_report_count IS NOT NULL OR DV1.medline_mesh_other_count IS NOT NULL) AND DV2.SPL_SPLICER_COUNT IS NOT NULL AND DV1.DRUG = CA.ANCESTOR_CONCEPT_ID AND DV2.DRUG = CA.DESCENDANT_CONCEPT_ID ORDER BY HOI LIMIT 100;
This query brings together MEDLINE data across publication types with SPL ADRs. Here again, It might be just useful to assign a count of ‘1’ to the “rolled up” count for the case that there is ANY SPL evidence for the drug-HOI and then provide the key to rows in the pivot table where that evidence exist.
Do you have any thoughts about this? Where should the “rolled up” counts reside? The pivot table would be the easiest for querying. Perhaps we could add two columns to the materialized view drug_HOI_evidence_view (ran in the script ohdsiTest3.psql); one indicating that there is evidence at a level descendant to the drug concept and the other indicating the rows where the evidence is elsewhere in the pivot table.
Thanks,
-Rich