This part of the tutorial will have you create a worklist of completed exams in a table to learn about the object-relational mapping of the data model by writing queries with the SDK.
Prerequisites¶
- Familiar with
SQL
and relational database concepts (e.g. foreign key constraints and table joins). - Familiar with erb syntax.
Building a query¶
Bridge stores clinical data into a normalized, universal data model so that applications are portable across different instances, regardless of the source clinical information systems providing data to the platform. In this application, the starting point for queries will be Radiology exam data.
The SDK is built on Hibernate, an ORM framework, and provides a set of object-relational Mapping (ORM) classes built around the Bridge data model. Conceptually, an ORM maps tables in a relational database to classes. Each class represents a table in the database and each instance of that class represents a row or record in the table. Those classes also have methods built in that represent the relationships/foreign keys between the database tables. The advantages of using an ORM are to make a normalized database schema easier to navigate and provide a clean abstraction where data and common methods are related, at the expense of some performance overhead.
The entity manager¶
The first stage of any query in the SDK is building an entity manager
. The entity manager
is an object that contains the database connection and built-in caching. This caching manifests in the queries and ORM objects. You will build controller methods to handle creation and destruction of entity managers. Since this is a common task throughout the application, put these methods in the application controller at app/controllers/application_controller.rb
:
class ApplicationController < ActionController::Base
# Prevent CSRF attacks by raising an exception.
# For APIs, you may want to use :null_session instead.
protect_from_forgery with: :exception
def get_entity_manager
@entity_manager ||= Java::HarbingerSdk::DataUtils.getEntityManager
end
def close_entity_manager
@entity_manager.close if [email protected]_manager.nil?
end
end
This constructs two methods to handle creation and destruction of an @entity_manager
instance variable. To make the methods cleaner, you can take advantage of Rails' before_filter
and after_filter
class methods. Edit app/controllers/worklist_controller.rb
to add the following lines at the top of the class:
class WorklistController < ApplicationController
before_filter :get_entity_manager
after_filter :close_entity_manager
#...
end
These filters will ensure that every WorklistController
request will have an @entity_manager
variable with a new entity manager. Additionally, once the full request is complete, the @entity_manager
will be closed so to prevent wasting a database connections. You can refer to @entity_manager
in the WorklistController
methods.
Tip - Rails' filters can come in handy for a number of things. You will use them again to implement authentication and authorization. Review the Rails controller guide and the API documentation to learn more about filters.
The SDK query library¶
To build a query, you will use the SDK query library. While it is possible to use SQL directly through the SDK, using the query library is highly recommended. An abstraction layer between the application and data model helps keep your apps working without needing updates when the data model is changed. It also allows you to take advantage of the pre-defined relationships, object-relational mapping for quick traversal of data, and built-in helper functions that go beyond SQL.
To build a query that will yield all the radiology exams in a "complete" status, filter by the radiology exam's universal event type.
Best Practice - Any time you need to filter by a particular workflow status, use the universal event type. While may be important to display an particular clinical system's native mnemonic representation of a worklow status (found in external system statuses), these are not universally understood or a reliable / portable way to filter. For example, one clinical information system may use "C" that represent a completed exam, while another system uses "C" to represent a "cancelled" status. Applications that do not use universal event types cannot be approved for distribution.
This is a SQL example of a query to select all rad_exams
with a current status mapped to the the universal event type of complete
. The SQL demonstrates what the SDK will do, to aid in understanding what happens in the query code.
SELECT re.*
FROM rad_exams re
LEFT JOIN external_system_statuses ess ON ess.id = re.current_status_id
LEFT JOIN universal_event_types uet ON uet.id = ess.universal_event_type_id
WHERE uet.event_type = 'complete';
This query involves three tables: rad_exams
, external_system_statuses
, and universal_event_types
. For a visual representation of the rad_exams
table, you can see the schema entity-relationship diagram. The query performs a join between rad_exams
and external_system_statuses
using rad_exams.current_status_id
as a foreign key and a join between external_system_statuses
and universal_event_types
using external_system_statuses.universal_event_type_id
as a foreign key. The WHERE
clause specifies to only return rows filtered by universal_event_type.event_type
equal to 'complete'
Note - Throughout the data model, the tables and foreign keys follow naming conventions, specified in the core data model documentation.
To execute the same query using the query library, start by creating a rad_exams
query object using the entity manager.
query = Java::HarbingerSdkData::RadExam.createQuery(@entity_manager)
By creating the query on the RadExam model, the root of the query (the first table in the FROM
clause) is set to rad_exams
. This is equivalent to the SQL:
SELECT re.* FROM rad_exams re;
Next, set up the joins and filtering. Start by using implicitly (automatically) defined joins then referring to related elements with a property string. This is equivalent to the WHERE
clause in the SQL.
Tip - When writing a complex query or exploring the query library, you can test things out on the Rails console. The console provides an interactive Ruby interpreter with the entire application environment loaded. Use the
launch_dev_console
script created in Part 3 to interactively test a query as you build it.
expression = query.equal(".currentStatus.universalEventType.eventType","complete")
query.where(expression)
The method equal
specifies a SQL =
operator comparison. The first argument is a property string and the second is the literal string complete
. A property string
is special syntax for specifying fields of ORM objects. Property strings follow the scheme [Model].field_or_method[.optional_chain_of_field_or_method]
. The Model is the name of the model object (e.g. RadExam) and the field_or_method is a field of that model (table column) or relationship method (foreign key relationship). The [Model]
portion of the string is optional and is assumed to be the root
object of the query when the property string starts with .
. When using createQuery
, the root is the Model createQuery
was called on . In this code, it is a RadExam
. The property string .currentStatus.universalEventType.eventType
means join the table associated with the currentStatusId
foreign key, then join the table associated with the universalEventTypeId
foreign key, and finally refer to the field event_type
on that table. Finally, take the expression returned by the equal
method and set that to be the WHERE
clause using query.where(expression)
.
Note - Similar to the conventions used in the database schema, there are also conventions in the Model objects. Each table is changed from plural to singular and uses camel case (not underscores). The
rad_exams
table has a corresponding modelRadExam
.
In practice, you will often set a limit on the result set and you will want to work with the returned objects as a Ruby array:
@exams = query.limit(100).list().to_a
Best Practice - The
list
method returns a Java Object that implements the Collection interface. Since this is a Ruby application, it is simpler to use the native Ruby Array object by calling theto_a
method on the return oflist
.
The entire WorklistController
index
method:
def index
query = Java::HarbingerSdkData::RadExam.createQuery(@entity_manager)
expression = query.equal(".currentStatus.universalEventType.eventType","complete")
query.where(expression)
@exams = query.limit(100).list().to_a
end
Join order can have a significant impact on query performance, so the query can be rewritten use explicit joins instead:
def index
query = Java::HarbingerSdkData::RadExam.createQuery(@entity_manager)
query.join(".currentStatus")
query.join("CurrentStatus.universalEventType")
query.where(query.equal(".currentStatus.universalEventType.eventType","complete"))
@exams = query.limit(100).list().to_a
end
Before displaying the results, add an ORDER BY
clause to sort the results by the oldest exam (based on exam completion time). This timestamp is found in the rad_exam_times
table as the column end_exam
. The equivalent SQL query:
SELECT re.*
FROM rad_exams re
LEFT JOIN external_system_statuses ess ON ess.id = re.current_status_id
LEFT JOIN universal_event_types uet ON uet.id = ess.universal_event_type_id
LEFT JOIN rad_exam_times ret ON ret.rad_exam_id = re.id
WHERE uet.event_type = 'complete'
ORDER BY ret.end_exam DESC
LIMIT 100;
The clause to add the ORDER BY
to the query object (with implicit join):
query.order(".radExamTime.endExam desc")
The order
method takes a special property string that incorporates a direction. If no direction is given, the default is ascending order. The two direction options are "asc" and "desc" for ascending and descending order, just like SQL.
The final WorklistController
index
method looks like this:
def index
query = Java::HarbingerSdkData::RadExam.createQuery(@entity_manager)
expression = query.equal(".currentStatus.universalEventType.eventType","complete")
query.where(expression)
query.order(".radExamTime.endExam desc")
@exams = query.limit(100).list().to_a
end
The WorklistController
now has an instance variable @exams
that can be used in the view.
Tip - When working with the query library, particularly when building a dynamic query from user input, it can be difficult to predict the emitted SQL. The query object has two methods provide this data:
toSQL
andtoHQL
.toSQL
prints the exact SQL that will be executed with parameters separated.toHQL
show the Hibernate Query Language equivalent of the query, using model names instead of the table names.
Summary¶
To build a query using the SDK's query library:
- Build an entity manager (use a
before_filter
) - Create a query object with the
createQuery
method on the model that will be the root of the query (ex:Java::HarbingerSdkData::RadExam.createQuery(@entity_manager)
- Create clauses with the corresponding method and the builder methods of the query object (see Querying for details).
- Call the
list()
method to get the results - Close the entity manager connection after all querying and result manipulation is complete (use an
after_filter
)
Building a table¶
The @exams
instance variable is an Array of exams that can be used to build a table. Start by showing a list of exams, edit app/views/worklist/index.html.erb
:
<div class="container-fluid">
<div class="row">
<div class="col-xs-12">
<h1>Ready to read worklist</h1>
<%= debug @exams %>
</div>
</div>
</div>
Tip - Rails provides a helper method called
debug
that is a prettyprint in HTML. It prettyprints objects wrapped in a<pre>
tag to display well in a browser. It's a helpful way to do simple debugging. For more advanced debugging, try pry.
Browse to http://server.example.com:3000/worklist
. You will see a list of RadExam objects (ex: #<Java::HarbingerSdkData::RadExam:0x39ff096>
). Start building the table by iterating over the @exams
Array and printing a table row for each RadExam object. Replace <%= debug @exams %>
with:
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>Accession #</th>
<th>Patient MRN</th>
<th>Patient Name</th>
<th>Completed At</th>
</tr>
</thead>
<tbody>
<% @exams.each do |exam| %>
<tr>
<td><%= exam.accession %></td>
<td><%= exam.patientMrn.mrn %></td>
<td><%= exam.patientMrn.patient.name %></td>
<td><%= exam.radExamTime.endExam %></td>
</tr>
<% end %>
</tbody>
</table>
This renders a simple HTML table. The each
method is passed a block that takes an argument (an exam from the @exams
Array). The exam object is a representation of the record/row in the rad_exams
table. It has methods for all the fields and relationships associated with the record. Calling exam.accession
will return the value of the accession
column for that record. Calling exam.patientMrn
will return the associated record in the patient_mrns
table. These operations can be chained together to quickly traverse the normalized data model with ease, e.g. exam.patientMrn.patient.name
. That statement uses the relationships between rad_exams
, patient_mrns
, and patients
, running the queries to retrieve the associated records dynamically and caching the results.
To improve the table, the date format could be made easier to read, and it would be helpful to add an Age column. Define these helper functions in app/helpers/application_helper.rb
:
module ApplicationHelper
# Transform java sql tiem into a ruby time object
def parse_time(java_sql_time)
Time.parse(java_sql_time.to_s) if java_sql_time
end
# Format a time using Time.strftime
def formatd(time_object)
#Month ('Jan'), Day ('1..31'), Hour (00..23):Minute (00..59)
parse_time(time_object).strftime("%b %e, %H:%M") if time_object
end
# Calculate the age of a time using Rails' distance_of_time_in_words
def age(time_object)
distance_of_time_in_words(Time.now,parse_time(time_object)) if time_object
end
end
Best Practice - Data in real-world systems is often incomplete or formatted in unexpected ways. Handle potential
null
(Rubynil
) values any time you use dot notation to traverse objects or run methods. This is handled in the code above with an inlineif
statement. Some situations are easier to handle with the.try
method. Regardless of technique, expect missing or malformed data in applications.
Add the new column to the header and body of the table and change the formatting of "Completed At", using the new helper methods in the view (app/views/worklist/index.html.erb
):
<!-- Heading -->
<th>Age</th>
<!-- Body column -->
<td><%= formatd(exam.radExamTime.endExam) %></td>
<td><%= age(exam.radExamTime.endExam) %></td>
The final app/views/worklist/index.html.erb
:
<div class="container-fluid">
<div class="row">
<div class="col-xs-12">
<h1>Ready to read worklist</h1>
<table class="table table-bordered table-striped">
<thead>
<tr>
<th>Accession #</th>
<th>Patient MRN</th>
<th>Patient Name</th>
<th>Completed At</th>
<th>Age</th>
</tr>
</thead>
<tbody>
<% @exams.each do |exam| %>
<tr>
<td><%= exam.accession %></td>
<td><%= exam.patientMrn.mrn %></td>
<td><%= exam.patientMrn.patient.name %></td>
<td><%= formatd(exam.radExamTime.endExam) %></td>
<td><%= age(exam.radExamTime.endExam) %></td>
</tr>
<% end %>
</tbody>
</table>
</div>
</div>
</div>
Summary¶
ORM objects are a representation of the records in particular tables. Accessing the values is done with dot notation. Take advantage of relationships between models/tables to access data in other models.