Writing an SQL Query

Quick refresher, we are examining a script that sends an email to students to remind them that they have a class tomorrow. We need to get a list of classes that start tomorrow from our database. We do that by using a Cayanne ObjectSelect.

def classesStartingTomorrow = ObjectSelect.query(CourseClass)      .where(CourseClass.IS_CANCELLED.eq(false))      .and(CourseClass.START_DATE_TIME.ne(null))      .and(CourseClass.START_DATE_TIME.between(tomorrowStart, tomorrowEnd))      .select(context) A variable must store our list of classes for later use. We name this variable ‘classesStartingTomorrow’. We then specify that we are using the Cayenne ObjectSelect to query our database for ‘CourseClass’ objects. We dont want every class in your database to be loaded into you variable so we need to specify some conditions. The conditions follow an SQL query format. The first condition is specified by WHERE and each subsequent condition is specified by AND.

The first condition checks if a classes is cancelled. So, a condition is needed to only get CourseClasses that have their property IS_CANCELLED equal to (abbreviated as eq) false.

.where(CourseClass.IS_CANCELLED.eq(false))

But we need additonal conditions. We also only want classes that start tomorrow. This means we want classes that start between 12:00am tomorrow and 12:00am the next day. Luckily we have already created two variables that represent these dates to help check if a class starts in this range.

.and(CourseClass.START_DATE_TIME.between(tomorrowStart, tomorrowEnd)) This retrieves classes that have a start time between 12:00am tomorrow, and 12:00am the day after.

Finally our script wants to make sure the classes retreived have a start date set. ‘Self-paced’ classes in OnCourse do not have a start date, as they can be started at any time. The startDateTime field for all self-placed classes is set to NULL. NULL is a special character that means ‘there is no data in this field’. We want to check that the startDateTime field has a value, that is, the startDateTime field does not equal (abbreviated as ne) NULL.

.and(CourseClass.START_DATE_TIME.ne(null))

Finally, we have to use our ‘context’ variable to talk to our database.

.select(context) This will allow our script to retrieve that data from the database that matches our conditions. With this ObjectSelect, we now have a list of all classes that are not cancelled and start tomorrow.

Filtering lists with Groovy closures

One of Groovy’s most powerful feature are Groovy closures. Groovy closures are short blocks of executable code. One of the most useful applications of a Groovy closure is the ability to ‘filter’ a list using a findAll{} closure. A findAll{} closure will filter a list of objects to only include items that meet the criteria in the closure.

We want to filter our list to only include classes that have met their minimum number of enrolments. This is done by checking the total number of current enrolments is larger then the specified minimum number of enrolments. This can be done by referencing the successAndQueuedEnrolments and minimumPlaces properties of a CourseClass.

classesStartingTomorrow.findAll { cc -> cc.successAndQueuedEnrolments.size() >= cc.minimumPlaces }

As classesStartingTomorrow is a list, each item is iterated through to see if it meets the criteria. The ‘cc’ variable represents the each class as they are iterated upon. We can see in the DSL that successAndQueuedEnrolments returns a list of enrolments. We can check how many enrolments there are in this list by calling the size() method. When cc.successAndQueuedEnrolments.size() >= cc.minimumPlaces evaluates to true, the iterated class is added to the list that will be returned.

Now we have filtered and returned our list of classes, we need to get every enrolment from all these classes. For this purpose, we can use Groovy’s dot-spread operator (*.). The dot-spread operator will call a method or propertie for all items in a list and will return a collection of the resulting objects. We can use the dot-spread operator on our filtered list of classes to get a collection of successful enrolments from every class. We can further transform this collection to an easy-iterable list using the flatten() method.

Finally, we want to iterate through this list and send an email to each enrolled student. We can use another closure; each{}. This closue will execute the code inbetween the braces for each object in the list:

classesStartingTomorrow.findAll { cc ->     cc.successAndQueuedEnrolments.size() >= cc.minimumPlaces }*.successAndQueuedEnrolments.flatten.each { enrolment ->      } As before, ‘enrolment’ represents the each enrolment as it is being iterated upon.

Creating an email using OnCourse scripts

We want to create an email for each enrolment. This can be done using the OnCourse EmailSpec.

Emails must be sent to a Contact or email address. You can get a Contact from an Enrolment following the join from an Enrolment object to its Student, and then from the Student to the related Contact object:

to enrolment.student.contact

Emails must also have a template. We can get a template by specifiying the name of the template in your onCourse application you want to use

template “ … “

Bindings is the data object passed to the email template. Each templates accept objects of different types. You can check what object type an email template accepts by viewing the email template and looking at the ‘Entity’ field in the top right hand corner.

bindings enrolment : enrolment This will pass our enrolment variable being iterated on to the email template. email {     template “Student notice of class commencement”     bindings enrolment: enrolment     to enrolment.student.contact }

As we want this to be sent to every enrolment in our list, we place this block of code inside the each{} closure (as we want it executed on each record in our list).

classesStartingTomorrow.findAll { cc ->     cc.successAndQueuedEnrolments.size() >= cc.minimumPlaces }*.successAndQueuedEnrolments.flatten.each { enrolment ->     email {         to enrolment.student.contact         template “Student notice of class commencement”         bindings enrolment: enrolment      } }

…And we are done!

The only additional part of this script is the closing brace of the run() function.

As you can see that scripting in onCourse is has many applications to expand and automate your business processes.

You can explore massive amount of onCourse scripts available in our OnCourse script repository. Want to get even Groovy-er? Check out the Groovy docs to learn what is possible. Got an idea for a cool script? Drop us a line!