| OHDSI Home | Forums | Wiki | Github |
Dear Ohdsiers,
Could someone tell me where is the code that handles this Webapi api call ? Please specify files and lines if possible.
/WebAPI/cdmresults/{resource}/condition
It is in order to debug why I get empty array when I supposed to have data there.
Thank you in advance.
The root of the call starts here:
This part of the code has been made very generic to treat all domain treemap (the top-view of a domain like condition) as the same, so it looks at the URL path to determine which domain (in this case condition) and then will fetch the treemap data for that condition in a generic way (ie: uses a templated query to fetch the treemap data from specific tables).
It also uses a caching layer that can make it difficult to follow through the code because as you are stepping through the code, you won’t see how the caching layer fetches the data from the cache tables (achilles_cache) . If you think your data in your results schema’s ‘achilles_results’ table is correct but you don’t seem to see the correct data, you can truncate the webapi’s achilles_cache table so that it will fetch the results data from your CDM and then cache it in webapi.
Thank you very much, @Chris_Knoll
I have found that I am not using achilles cache, the table is not even created.
Also, following the execution of WebAPI, i have followed to this SQL script execution.
In my case, the path to treemap.sql script is inside the docker container.
I think the script execution with my data result in an empty set.
I will provide feedback if i have any problem that i can not resolve myself.
This was my process after finding treemap.sql script
ORIGINAL QUERY FROM TREEMAP
SELECT
concept_hierarchy.concept_id,
CONCAT(
isNull(concept_hierarchy.level4_concept_name, 'NA'), '||',
isNull(concept_hierarchy.level3_concept_name, 'NA'), '||',
isNull(concept_hierarchy.level2_concept_name, 'NA'), '||',
isNull(concept_hierarchy.level2_concept_name, 'NA'), '||',
isNull(concept_hierarchy.concept_name, 'NA')
) AS concept_path,
ar1.count_value AS num_persons,
round(1.0 * ar1.count_value / denom.count_value, 5) AS percent_persons,
round(1.0 * ar2.count_value / ar1.count_value, 5) AS records_per_person
FROM (SELECT *
FROM @results_database_schema.achilles_results WHERE analysis_id = 400) ar1
INNER JOIN
(SELECT *
FROM @results_database_schema.achilles_results WHERE analysis_id = 401) ar2
ON ar1.stratum_1 = ar2.stratum_1
INNER JOIN
@results_database_schema.concept_hierarchy concept_hierarchy
ON CAST(CASE WHEN ar1.analysis_id = 400 THEN ar1.stratum_1 ELSE null END AS INT) = concept_hierarchy.concept_id
AND concept_hierarchy.treemap='Condition'
,
(SELECT count_value
FROM @results_database_schema.achilles_results WHERE analysis_id = 1) denom
ORDER BY ar1.count_value DESC
Translated with Sqlrender online using postgresql dialect and substitute the ohdsi sql parameter with the value omop_cdm_results
SELECT
concept_hierarchy.concept_id,
CONCAT(
COALESCE(concept_hierarchy.level4_concept_name,'NA'), '||',
COALESCE(concept_hierarchy.level3_concept_name,'NA'), '||',
COALESCE(concept_hierarchy.level2_concept_name,'NA'), '||',
COALESCE(concept_hierarchy.level2_concept_name,'NA'), '||',
COALESCE(concept_hierarchy.concept_name,'NA')
) AS concept_path,
ar1.count_value AS num_persons,
ROUND(CAST(1.0 * ar1.count_value / denom.count_value AS NUMERIC),5) AS percent_persons,
ROUND(CAST(1.0 * ar2.count_value / ar1.count_value AS NUMERIC),5) AS records_per_person
FROM (SELECT *
FROM omop_cdm_results.achilles_results WHERE analysis_id = 400) ar1
INNER JOIN
(SELECT *
FROM omop_cdm_results.achilles_results WHERE analysis_id = 401) ar2
ON ar1.stratum_1 = ar2.stratum_1
INNER JOIN
omop_cdm_results.concept_hierarchy concept_hierarchy
ON CAST(CASE WHEN ar1.analysis_id = 400 THEN ar1.stratum_1 ELSE null END AS INT) = concept_hierarchy.concept_id
AND concept_hierarchy.treemap='Condition'
,
(SELECT count_value
FROM omop_cdm_results.achilles_results WHERE analysis_id = 1) denom
ORDER BY ar1.count_value DESC
After executing it,
i got a table with 12 rows filled up of concepts, num_persons column, percentage and records per person. Although this WebAPI endpoint /WebAPI/cdmresults/{resource}/condition still remains returning empty set.
I have checked code execution trace and it leads to me the most probably issue is table qualifiers are wrong.
So I decided to check the table qualifiers
looking this WebAPI endpoint /WebAPI/source/connection/{source}
Could you see any issue in my table qualifiers ?
@Chris_Knoll Thank you in advance.
That data is cached in achilles_cache I believe, so you can try the above suggestion:
You were right, I thought achilles_cache table was not in the webapi schema.
I have just truncated the contents and it works fine.
Thank you very much @Chris_Knoll
