BridgeClient::Query¶

The Query module allows you to chain SQL statements together as methods in order to write a query and send to the Platform API.

Simple query with FROM¶

All queries must contain a from method, so the simplest example of a valid query would be:

BridgeClient::Query.from('clinical_roles').list

=> [{"updated_at"=>"2020-1-24T20:10:56Z", "id"=>1, "description"=>"clinical radiologist (including residents, attendings, etc.)", "department_id"=>1, "clinical_role"=>"radiologist"}, {"updated_at"=>"2020-1-24T20:10:56Z", "id"=>2, "description"=>"resident", "department_id"=>1, "clinical_role"=>"resident"}]

NOTE Calling from by itself will not execute the query; it returns an object that can either be composed with other query methods or have one of its methods called that will execute the query.

Getting results¶

list executes the query and returns an array of hashes in which each key is a column (note that all columns are returned if an explicit select is not provided).

Another option is to use first, which returns the first result

BridgeClient::Query.from('clinical_roles').first

=> {"updated_at"=>"2020-1-24T20:10:56Z", "id"=>1, "description"=>"clinical radiologist (including residents, attendings, etc.)", "department_id"=>1, "clinical_role"=>"radiologist"}

Chaining methods to create a larger query¶

You can chain together other methods: joins, where, group, order, limit, and offset.

BridgeClient::Query
  .from('clinical_roles')
  .select('clinical_role')
  .order('id desc')
  .limit(2)

=> [{"clinical_role"=>"researcher"}, {"clinical_role"=>"ai-staff"}]

Joining tables¶

Joins can be done in two ways. The first is by using dot notation within your select statement. (See the API documentation for details on dot notation)

BridgeClient::Query.from('clinical_roles').select('clinical_role').select('.departments.department').first

=> {"department"=>"Radiology", "clinical_role"=>"radiologist"}

Keep in mind that when joining via dot notation, you have to follow normal SQL rules to specify tables for column names that appear in more than one of the joined tables. In the example below, we have to specify the clinical_roles table name in our select and order for the id column, since that column also exists in the now joined departments table.

BridgeClient::Query
  .from('clinical_roles')
  .select('clinical_roles.id')
  .select('clinical_role')
  .select('.department_id.department')
  .order('clinical_roles.id desc')
  .first

=> {"id"=>21, "department"=>"Radiology", "clinical_role"=>"researcher"}

You can also use the join method to join tables explicitly. Since you can use any type of join, you must provide the entire join string within the argument.

BridgeClient::Query
  .from('clinical_roles')
  .join('LEFT JOIN departments on departments.id = clinical_roles.department_id')
  .select('clinical_role')
  .select('department')
  .first

=> {"department"=>"Radiology", "clinical_role"=>"radiologist"}

Using parameters in your query¶

An array of substitution parameters can be provided in any of the methods as the second argument. Parameters should be used as $1, $2, $3, etc in the string of the first argument where the number reflects the parameter's index in the second argument.

WARNING Putting a variable directly into the conditions string passes the variable to the database as-is. The variable will be unescaped, opening your application to SQL injection attacks. Never put parameters directly inside the conditions string -- always use the parameters argument instead.

BridgeClient::Query
  .from('clinical_roles')
  .join('LEFT JOIN departments on departments.id = clinical_roles.department_id')
  .select('clinical_role')
  .select('department')
  .where('clinical_roles.id = $1', 21)

=> {"department"=>"Radiology", "clinical_role"=>"researcher"}

Debugging your query¶

If you need to debug your query to find what BridgeClient is sending to the Platform API, you can use the .to_s method.

BridgeClient::Query
  .from('clinical_roles')
  .join('LEFT JOIN departments on departments.id = clinical_roles.department_id')
  .select('clinical_role')
  .select('department')
  .to_s

=> "SELECT clinical_role, department FROM clinical_roles LEFT JOIN departments on departments.id = clinical_roles.department_id"

Exceptions¶

If there is an issue detected in the SQL, a BridgeClient::SqlStatementException is raised.

BridgeClient::Query.from('clinical_roles').select('critical_roll').first

BridgeClient::SqlStatementException: SQL Statement resulted in error: column "critical_roll" does not exist
in sql SELECT critical_roll FROM clinical_roles     LIMIT 1

If a larger issue occurs, a RestClient::ExceptionWithResponse will be raised.