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.
- Familiar with
SQLand 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
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'
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
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:
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.current_status_id as a foreign key and a join between
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_consolescript created in Part 3 to interactively test a query as you build it.
expression = query.equal(".currentStatus.universalEventType.eventType","complete") query.where(expression)
equal specifies a SQL
= operator comparison. The first argument is a property string and the second is the literal string
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
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_examstable has a corresponding model
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
listmethod 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 the
to_amethod on the return of
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):
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.
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
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:
toSQLprints the exact SQL that will be executed with parameters separated.
toHQLshow the Hibernate Query Language equivalent of the query, using model names instead of the table names.
To build a query using the SDK's query library:
- Build an entity manager (use a
- Create a query object with the
createQuerymethod on the model that will be the root of the query (ex:
- 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
Building a table¶
@exams instance variable is an Array of exams that can be used to build a table. Start by showing a list of exams, edit
<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
debugthat 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.
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
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
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
nil) values any time you use dot notation to traverse objects or run methods. This is handled in the code above with an inline
ifstatement. Some situations are easier to handle with the
.trymethod. 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 (
<!-- Heading --> <th>Age</th> <!-- Body column --> <td><%= formatd(exam.radExamTime.endExam) %></td> <td><%= age(exam.radExamTime.endExam) %></td>
<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>
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.