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 model RadExam.

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 the to_a method on the return of list.

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 and toHQL. 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:

  1. Build an entity manager (use a before_filter)
  2. 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)
  3. Create clauses with the corresponding method and the builder methods of the query object (see Querying for details).
  4. Call the list() method to get the results
  5. 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 (Ruby nil) values any time you use dot notation to traverse objects or run methods. This is handled in the code above with an inline if 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.