search

ncats / RaMP-DB

27 stars 13 forks source link

Chebi missing in SQL dump #82

Closed marieBvr closed 2 months ago

marieBvr commented 3 months ago

Hi, I seem to have successfully loaded the latest sql dump into mySQL workbench.

I want to query the metabolite_class with chebi IDs as in the interface, but I can't find any.

Is this normal? Should I do a conversion before querying?

Thanks

KeithKelleher commented 3 months ago

Hello, If I understand correctly, you're querying the mysql database with chebi IDs to get data from metabolite_class. The UI queries this data through R functions like chemicalClassSurvey. In that case, the Ids are typically inferred from data in the source table. Here's an example query like the UI would be doing.

SELECT DISTINCT met_class.ramp_id, src.sourceId, GROUP_CONCAT(DISTINCT src.commonName) AS common_names, met_class.class_level_name, met_class.class_name, met_class.source AS source, COUNT(DISTINCT (met_class.class_source_id)) AS directIdClassHits FROM metabolite_class met_class, source src WHERE src.rampId = met_class.ramp_id AND src.sourceId IN ('chebi:196991') GROUP BY met_class.ramp_Id , src.sourceId , met_class.class_level_name , met_class.class_name , met_class.source;

Let me know if that helps, or if there's anything else. Keith

Mathelab commented 3 months ago

@marieBvr

marieBvr commented 2 months ago

Hi @KeithKelleher , My apologies for the late reply. We have tested the query and we have recovered the identifiers. We will see how to connect this request to another one we are making. We need to dig a little deeper to understand everything.

Thanks for your help! Marie

KeithKelleher commented 2 months ago

thanks @marieBvr Let us know if you have any questions. Feel free to reopen the github issue.