Custom Relationship Queries

Relationship queries are automatically generated by the browser to retrieve the count and records connected through an ontology relationship. This approach works well for most cases.

However, for specific relationships, custom queries may be preferred. These can be defined in a relationship_queries.yaml file within the administrative workspace.

Custom Query Structure

The YAML file is structured by the relationship name, with entries for a counts query and an entities query. Each query is written as a Nunjucks template.

In the template, the labels and ids arrays are provided in the context. These contain SQL-safe (quoted) values for the entity_label and entity_id fields of the entities for which the counts or connected entities are required.

The counts query must return results with {entity_id, cardinality} where entity_id is the sought entity_id and cardinality is the count of connected entities.

The entities query must return results with {entity_id, entity_type, mapping_entity_id} where mapping_entity_id is the sought entity_id, while entity_type and entity_id refer to the connected entity.

Example:

identifier_spotted_at:
  counts: |
    SELECT source_entity_identifier as entity_id, count(1) as cardinality 
          FROM timbr.position
          WHERE source_entity_identifier in ({{ labels | safe }})
          GROUP BY source_entity_identifier
  entities: |
    SELECT entity_id, entity_type,
      source_entity_identifier as mapping_entity_id
          FROM timbr.position
          WHERE source_entity_identifier in ({{ labels | safe }})