Custom Queries

Use the following connection settings to connect to the ESP Database. You can use any database tool of your choice, but we would recommend DBeaver for this course.

{
  "host": "localhost",
  "user": "l7esp",
  "port": 1487,
  "ssl": false,
  "database": "lab7",
  "password": ""
}

If you’re trying to pull data from ESP and none of the APIs will give you exactly what you want, your next option is to write a SQL query. If we were going to walk through a query that gives us Workflow Turnaround Time we’d want to consider:

Most commonly-used tables for implementations:

Baseline query for selecting LIMS data:

select rvar.name field_name, val.value field_value, rsamp.name sample_name, rprotdef.name protocol_name
  from resource_val val
  join resource_var var on val.resource_var_id = var.resource_var_id
  join resource rvar on var.resource_var_id = rvar.resource_id
  join step_instance_sample sis on val.step_instance_sample_id = sis.association_id
  join workflowable_resource samp on sis.sample_id = samp.sample_id
  join resource rsamp on samp.sample_id = rsamp.resource_id
  join step_instance_base sib on sis.instance_id = sib.instance_id
  join step_definition_base sdb on sib.definition_id = sdb.definition_id
  join protocol_definition pd on sdb.definition_id = pd.protocol_id
  join resource rprotdef on pd.protocol_id = rprotdef.resource_id

name: New Query
description: |+
  New Query Description
query: |
  select resource.name from resource where uuid in :uuids
parameters:
  uuids:
    description: UUIDS to fetch
    type: list