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.