Can't use Region and Country filter in combination

[johanvonboer]

[johanvonboer]

request payload: {"requestId":8,"requestType":"populate","targetCode":"region","triggerCode":"region","domainCode":"","facetConfigs":[{"facetCode":"country","position":1,"picks":[],"textFilter":""},{"facetCode":"region","position":2,"picks":[],"textFilter":""}]}

SqlQuery": "\n SELECT category, count(value) AS count\n FROM (\n SELECT region.location_id AS category, tbl_analysis_entities.analysis_entity_id AS value\n FROM tbl_analysis_entities\n INNER JOIN tbl_physical_samples ON tbl_physical_samples.\"physical_sample_id\" = tbl_analysis_entities.\"physical_sample_id\" \t\t\t\t\t\n INNER JOIN tbl_sample_groups ON tbl_sample_groups.\"sample_group_id\" = tbl_physical_samples.\"sample_group_id\" \t\t\t\t\t\n INNER JOIN tbl_sites ON tbl_sites.\"site_id\" = tbl_sample_groups.\"site_id\" \t\t\t\t\t\n INNER JOIN tbl_site_locations ON tbl_site_locations.\"site_id\" = tbl_sites.\"site_id\" \t\t\t\t\t\n INNER JOIN tbl_locations AS countries ON countries.\"location_id\" = tbl_site_locations.\"location_id\" \t\t\t\t\t\n INNER JOIN tbl_datasets ON tbl_datasets.\"dataset_id\" = tbl_analysis_entities.\"dataset_id\" \t\t\t\t\t\n INNER JOIN tbl_locations AS region ON region.\"location_id\" = tbl_site_locations.\"location_id\" \n WHERE 1 = 1\n AND countries.location_type_id=1\n GROUP BY region.location_id , tbl_analysis_entities.analysis_entity_id\n ) AS x\n GROUP BY category;\n ",

[johanvonboer]

Need to include site_location for each facet:

SELECT category, count(value) AS count
FROM (
SELECT region.location_id AS category, tbl_analysis_entities.analysis_entity_id AS value
FROM tbl_analysis_entities
INNER JOIN tbl_physical_samples ON tbl_physical_samples."physical_sample_id" = tbl_analysis_entities."physical_sample_id"           
INNER JOIN tbl_sample_groups ON tbl_sample_groups."sample_group_id" = tbl_physical_samples."sample_group_id"           
INNER JOIN tbl_sites ON tbl_sites."site_id" = tbl_sample_groups."site_id"           
INNER JOIN tbl_site_locations ON tbl_site_locations."site_id" = tbl_sites."site_id"           
INNER JOIN tbl_locations AS countries ON countries."location_id" = tbl_site_locations."location_id"           
INNER JOIN tbl_datasets ON tbl_datasets."dataset_id" = tbl_analysis_entities."dataset_id"           
INNER JOIN tbl_locations AS region ON region."location_id" = tbl_site_locations."location_id" 
WHERE 1 = 1
AND countries.location_type_id=1
GROUP BY region.location_id , tbl_analysis_entities.analysis_entity_id
) AS x
GROUP BY category;

[roger-mahler]

The problem is caused by facet's sharing table routes in an incorrect way. Easiest solution is to create a shortcut between site and location that can be used as target for both region and countries. The problem is caused by limitations in the query builder that reuses table routes. In this case the same site_locations target table is used for both location targets which generates a faulty query. Easiest solution is to create a shortcut between site and location that can be used as target for both region and countries.

The shortcut view:

create or replace view facet.site_location_shortcut as
    select tbl_site_locations.site_id, tbl_locations.*
    from tbl_site_locations
    join tbl_locations using (location_id);

The compiled query that uses the shortcut:

 SELECT category, count(value) AS count
 FROM (
    SELECT region.location_id AS category, tbl_analysis_entities.analysis_entity_id AS value
    FROM tbl_analysis_entities
    INNER JOIN tbl_physical_samples ON tbl_physical_samples."physical_sample_id" = tbl_analysis_entities."physical_sample_id"           
    INNER JOIN tbl_sample_groups ON tbl_sample_groups."sample_group_id" = tbl_physical_samples."sample_group_id"           
    INNER JOIN tbl_sites ON tbl_sites."site_id" = tbl_sample_groups."site_id"           
    INNER JOIN facet.site_location_shortcut as region on region.site_id = tbl_sites.site_id
    INNER JOIN facet.site_location_shortcut as countries on countries.site_id = tbl_sites.site_id
    INNER JOIN tbl_datasets ON tbl_datasets."dataset_id" = tbl_analysis_entities."dataset_id"           
    WHERE 1 = 1
      AND countries.location_type_id=1
    GROUP BY region.location_id, tbl_analysis_entities.analysis_entity_id
 ) AS x
 GROUP BY category;

Actions to implement solution:

• Add shortcut view
• Add view to facet system'r routing data (table, table_relations etc.)
• Change facets "Countries" and "Region" to use shortcut instead of computed shortest distance graph.

[roger-mahler]

Resolved by https://github.com/humlab-sead/sead_change_control/issues/260. Deployed to staging.