This page is here to highlight SQL query syntax differences illustrating the changes made to the Ilios database schema. This happens along with our switching of the API from the old version (v2) to the new version (v3).
The database schema you know and love will be available until the end of the year. We have made some changes to make retrieving data from Ilios easier, especially regarding Course, Session, and Program Year Objectives. There are other small changes, but the primary changes were made in the realm of Objectives.
The tables objective_x_objective
, objective
and session_description
have been removed as of 1/29/2021. Some of the potential SQL query syntax changes that may be necessary as a result of these tables being removed, along with the other schema changes, are detailed below.
/** This is for new and old syntaxes for retrieving Objective recordsfrom Ilios -- going to use a place holder for course_id [course_id]any valid course ID can be used **/​/** new method - API v3 - retrieve course objectives **//** only need to query one table `course_x_objective` **/SELECT course_objective_id, titleFROM course_x_objectiveWHERE course_id = [course_id];​/** old method - API v2 - course objectives **/SELECT o.objective_id, o.titleFROM objective oJOIN course_x_objective cxoON cxo.objective_id = o.objective_idWHERE cxo.course_id = [course_id];
In the Course Objective query outlined above, it is important to note the changes that are taking place with the design of the course_x_objective
table in particular.
v2 Schema (old) - course_x_objective
v3 Schema (new) - course_x_objective
Additional fields that are available in course_x_objective
in the new version of the database schema without having to join to other tables: ancestor_id
, title
, and active
. Title is the big win here. Objectives are no longer stored in one location objectives
and there is now no need to join to that table in order to retrieve Course Objectives.
/** new method - API v3 - session objectives **//** still need to join to session table which contains course ID **/SELECT sxo.objective_id, sxo.titleFROM session_x_objective sxoJOIN session s on s.session_id = sxo.session_idWHERE s.course_id = [course_id];​/** old method - API v2 - session objectves **/SELECT o.objective_id, o.titleFROM objective oJOIN session_x_objective sxo ON sxo.objective_id = o.objective_idJOIN session s ON s.session_id = sxo.session_idWHERE s.course_id = [course_id];
In the Session Objective query outlined above, it is important to note the changes that are taking place with the design of the session_x_objective
table in particular. The only reason a join to the session
table was performed is that we were running our query passed upon the course_id
field as a parameter and that field is contained in the session
table.
v2 Schema (old) - session_x_objective
v3 Schema (new) - session_x_objective
Additional fields that are available in session_x_objective
in the new version of the database schema without having to join to other tables: ancestor_id
, title
, and active
. Title is the big win here. Objectives are no longer stored in one location objectives
and there is now no need to join to that table in order to retrieve Session Objectives.
This also makes it easier to deal with Parent Objectives as we will soon see.
This next one will return all Session Objectives with their parent (Course) Objectives as well, whether this parentage has been established or not. In this case, the queries will be separated into their own code blocks with comments.
In the example below, we had to join to the objective
table twice - once to return the Session Objectives and again to return the Course Objectives. The parent relationship between these two Objective levels is defined using the objective_x_objective
table. The field parent_objective_id
returns the ID values used to join to objective
to retrieve the Objective details.
/** Old method - all session objectives with course objectives- parents or not **/SELECT s.session_id AS 'Session ID',s.title AS 'Session',o.title AS 'Session Objective',oxo.parent_objective_id AS 'Course Objective ID',o2.title AS 'Course Objective'FROM session sJOIN session_x_objective sxo ON sxo.session_id = s.session_idJOIN objective o ON o.objective_id = sxo.objective_idLEFT OUTER JOIN objective_x_objective oxoON oxo.objective_id = o.objective_idLEFT OUTER JOIN objective o2ON o2.objective_id = oxo.parent_objective_idWHERE s.course_id = [course_id];
In the example below, there is no need to join to either objective
or objective_x_objective
, both of which will be removed at the end of the year. Left outer joins are performed similarly in both versions of this query in order to return Session Objectives that are NOT linked to Course Objectives.
/** New method - all session objectives wth course objectives- parents (or not) **/SELECT s.session_id AS 'Session ID',s.title AS 'Session',sxo.title AS 'Session Objective',soxco.course_objective_id AS 'Course Objective ID',cxo.title AS 'Course Objective'FROM session sJOIN session_x_objective sxo ON sxo.session_id = s.session_idLEFT OUTER JOIN session_objective_x_course_objective soxcoON soxco.session_objective_id = sxo.session_objective_idLEFT OUTER JOIN course_x_objective cxoON cxo.course_objective_id = soxco.course_objective_idWHERE s.course_id = [course_id];
/** new method - API v3 - program year objectives **//** only one table needed in query - no joins required **/SELECT objective_id, title, competency_idFROM program_year_x_objectiveWHERE program_year_id = [program_year_id];/** need to provide program_year_id - [program_year_id] **/​/** old method - API v2 - program year objectives **//** two tables needed - one join **//** need to get the objective text from the `objective` tablerather than get it directly from `program_year_x_objective` **/SELECT o.objective_id, o.title, o.competency_idFROM objective oJOIN program_year_x_objective pyxoON pyxo.objective_id = o.objective_idJOIN program_year py on py.program_year_id = pyxo.program_year_idWHERE py.program_year_id = [program_year_id];/** need to provide program_year_id - [program_year_id] **/
NOTE: These v2 Queries will no longer run after the end of 2020.
/** Old method - all session objectives with course objectives- parents or not and program year objectives **//** Using this technique, it is required to join3 times to the Objective table -once each for Session, Course, and Program Year **/SELECT s.session_id AS 'Session ID',s.title AS 'Session',o.title AS 'Session Objective',o2.objective_id AS 'Course Objective ID',o2.title AS 'Course Objective',o3.objective_id AS 'Prog Year Obj ID',o3.title AS 'PY Obj'FROM session sJOIN session_x_objective sxoON sxo.session_id = s.session_idJOIN objective oON o.objective_id = sxo.objective_idLEFT OUTER JOIN objective_x_objective oxoON oxo.objective_id = o.objective_idLEFT OUTER JOIN objective o2ON o2.objective_id = oxo.parent_objective_idLEFT OUTER JOIN objective_x_objective oxo2ON oxo2.objective_id = o2.objective_idLEFT OUTER JOIN objective o3ON o3.objective_id = oxo2.parent_objective_idWHERE s.course_id = [course_id];
/** New method - all session objectives wth course objective parents(or not) and program year objectives **/SELECT s.session_id AS 'Session ID',s.title AS 'Session',sxo.title AS 'Session Objective' ,soxco.course_objective_id AS 'Course Objective ID',cxo.title AS 'Course Objective',coxpyo.program_year_objective_id AS 'Program Year Obj ID',pyxo.title AS 'Program Year Objective Text'FROM session sJOIN session_x_objective sxoON sxo.session_id = s.session_idLEFT OUTER JOIN session_objective_x_course_objective soxcoON soxco.session_objective_id = sxo.session_objective_idLEFT OUTER JOIN course_x_objective cxoON cxo.course_objective_id = soxco.course_objective_idLEFT OUTER JOIN course_objective_x_program_year_objective coxpyoON coxpyo.course_objective_id = cxo.course_objective_idLEFT OUTER JOIN program_year_x_objective pyxoON pyxo.program_year_objective_id = coxpyo.program_year_objective_idWHERE s.course_id = [coures_id];
​