Add Extended Age Query (EAQ) functionality

[grantfitzsimmons]

Dependent on #5178

All Expanded Age Queries (EAQ) logic will use start/end numbers, no names.

Filtering Criteria

Only nodes in the GeologicTimePeriod table with valid StartPeriod and EndPeriod values will be considered for these queries.

Range Calculation

The calculated ranges will utilize the lowest EndPeriod value and the highest StartPeriod value. Note that the scale progresses from oldest to most recent, with larger numbers in Millions of Years Ago (MYA) indicating more distant events, and smaller numbers indicating more recent events.

For MVP (and maybe beyond), EAQ searches will be just for COs

User Interface

This EAQ 'field' will be visible to the user in the query builder.

The user will select the field (named 'Age') and choose between two operators: Name and Range

When Name is selected, the user will enter the name of a node in the GeologicTimePeriod table. This is then turned into a range by the system, but the query will still show the name.

When Range is selected, the user will enter a start and end period value (similar to a 'between' query). If possible, this could also be extended to use all the operators numeric queries typically have, though the value is a range.

Users can choose to either run the query as 'strict', meaning only results that fall entirely within that range will be returned, or they can be 'non-strict', meaning any overlap will be returned. This is simply a yes or no option in the query line.

Workflow:

• User creates a query or modifies an existing one.
• User adds the EAQ field(s) to the query.
• The user can choose either to enter a name or a range of numbers.
• Upon running the query, the system computes start/end dates from the Chronostratigraphic name entered by the user, or it uses the range entered directly by them to search.
• The search can be Strict or Non-strict.
• The extended query will search the following places:
  • Relative Age (searches Chronostrat names, not verbatim fields or numbers)
    • AgeNameID – FK to geologictimeperiod (chronostrat tree)
      • Using this relationship, we will search the startPeriod and endPeriod fields included in the chronostrat tree record linked to the age record.
        • Range: startPeriod - endPeriod
  • Absolute Age (searches only absolute age and uncertainty fields)
    • No FK to geologictimeperiod (chronostrat tree)
    • For search purposes, take the AbsoluteAge and AgeUncertainty fields to compute a range which will be used in the query logic.
      • For example:
        • AbsoluteAge = 10 mya
        • AgeUncertainty = 5 mya
        • Computed Range: 15 - 5 mya
  • Paleo Context (searches chronosStrat and chronosStratEnd, not verbatim fields, numbers, biostrat, lithostrat, or any other fields)
    • ChronosStratID and ChronosStratEndID (less common) – FKs to geologictimeperiod (chronostrat tree)
      • Using this relationship, we will search the startPeriod and endPeriod fields included in the chronostrat tree record linked to the age record.
      • Compute the widest range for the two if ChronosStratEndID is present
      • Computed Range: startPeriod - endPeriod

[CarolineDenis]

Notes:

• If relative age and age in QB and value not the same than return nothing
• Add new attribute to field extra on front end and add new attribute 'displayInQB', value of new attribute 'yes' to age field
• When Age field in QB, no column in the return for it