How to Create Hierarchical queries

Hierarchical queries let users find child specimens based on their parent specimens' characteristics.

An example of a hierarchical query shipped with OpenSpecimen as a default query with the title 'Show details of a participant having DNA derived from Tumor Breast Frozen Tissue', and can be used as a reference.

Steps to build the query:

Navigate to the query module and create a new query.

Step 1: Add conditions for the parent specimens.

Step 2: Apply the “Hierarchical” operator

Step 3: Add conditions for the child specimens

Because EDTA Whole Blood can be processed into multiple specimen types, include a filter such as: Specimen >> Type Equals Plasma

Step 4: Reorder the filters.

I.e., Drag and drop the filters to group the parent-specimen conditions and the child-specimen conditions.
The goal is to form a logically correct expression where the hierarchical operator sits between the two groups.

Step 5: Click on ‘View Records’ --> Then use Columns to select the fields you want displayed.

The selected columns will appear for both parent and child specimens in the result view.

‘Specimen QC’ is a custom form, attached at ‘Specimen Forms’ level

Add conditions for the parent specimens.
Apply the “Hierarchical” operator.
Add conditions for the child specimens

Reorder the filters.
Click on ‘View Records’ --> Then use Columns to select the fields you want displayed.

Export after results load. Make a few tweaks in the spreadsheet program like updating column names and rearranging columns, to get the desired results.

Things to be aware of:

Currently, there is no way to select different columns for parent and child specimens.
The column names for parent and child specimens cannot be renamed individually.
It is not possible to rearrange columns across levels. All the parent specimen columns are displayed first and followed by the child specimens.
The temporal field is shown for the parent specimens only when the parent specimen contains data for the field used in a temporal expression.
Some fields are displayed only at the parent level since they have the same values across all levels. E.g. anatomic site, biohazard, specimen class, laterality, pathology status.
To display these fields for both parent and child levels, include them explicitly in the query edit page as follows: