Taxonomy + dating queries not working

[johanvonboer]

@visead reports:

Both of the following examples should return results, but don't. The connection between taxonomy and dating appears to be broken

[roger-mahler]

Is this still a problem? Could you give me request JSON?

[visead]

This is still a problem. Should be populated by connection from tbl_taxa_master through tbl_abundances, - analysis_entities - physical_samples - back to analysis_entities then tbl_relative_dates. tbl_relative_ages.relative_age_name is the field to show, physical_samples is the table to count recods.

[roger-mahler]

The filter then targets the wrong table. Currently, the "Geochronolgy" filter targets tbl_geochronology and not tbl_relative_dates. The reason the filter return no results when preceeded by Genus filter is that the filter tries to find the taxa via tbl_dating_material, which is an empty table.

SELECT *
FROM tbl_geochronology
JOIN tbl_dating_material ON tbl_dating_material."geochron_id" = tbl_geochronology."geochron_id"
JOIN tbl_taxa_tree_master ON tbl_taxa_tree_master."taxon_id" = tbl_dating_material."taxon_id"
JOIN tbl_taxa_tree_genera ON tbl_taxa_tree_genera."genus_id" = tbl_taxa_tree_master."genus_id"
JOIN tbl_analysis_entities ON tbl_analysis_entities."analysis_entity_id" = tbl_geochronology."analysis_entity_id"
WHERE TRUE
  AND (tbl_taxa_tree_genera.genus_id::text in ('12711', '761', '668', '13672', '15102', '14136'))
  AND (tbl_geochronology.age >= 0 and tbl_geochronology.age <= 860000)

[visead]

There are two problems in one issue here - one on 1) geochronology and one on 2) time periods (relative dates). Neither filters work. tbl_dating_material is not relevant for this query.

1) Should be populated by connection from tbl_taxa_master through tbl_abundances, - analysis_entities - physical_samples - back to analysis_entities then tbl_geochronology.

2) Should be populated by connection from tbl_taxa_master through tbl_abundances, - analysis_entities - physical_samples - back to analysis_entities then tbl_relative_dates. tbl_relative_ages.relative_age_name is the field to show, physical_samples is the table to count records.

Bonus issue: We should change the name of the filter 'Time periods' to just 'Periods' as there are many types of period in the database.

[roger-mahler]

Change request: 20240604_DML_GEOCHRONOLOGY_FACET.