Tuesday, October 29, 2013

Using OData complex filters in REST and CFML

Lately I have been researching into how to build REST web services.  I have been using Taffy to make this all happen which has been a great experience overall. I have always been a strong advocate of following standards whenever possible so I wanted to do so as well with my REST web services.  Through all my searching I stumbled upon this document of RESTful Best Practices - very good read, I highly recommend it.  One of the areas I really wanted to understand how to do right was around filtering in REST.  The Best Practices document mentioned using OData for complex filtering.  Once I saw this was possible I knew I had to have it.  Luckily for me there is a OData4J project but unlucky for me was the fact that no matter how much I searched I could not find anyone who used OData4J in CFML.  Now I am not saying that articles on this do not exist but I was not able to find any.  If anyone knows of any please pass them on to me so I can compare notes.

So this post is all about how to get OData4J working in CFML, specially just one method in OData4J - the method that parses the OData style filters into something we can easily use and therefore turn into usable SQL.  Be aware that for my purposes to get this working I only needed it to work in Railo (4.1) so I have not tested this in any ColdFusion Server versions.  I do not see any reason why this would not work but some minor tweaks may be necessary if it does not.

REST filter

So for example let's say for instance you pass the below to your REST web service.

https://mydomain.com/REST/People/?filter=startswith(firstName, 'd') and isActive eq 1

How do we go about turning that into usable SQL?  Let's take a look...

Download OData4J

First thing you will need is the jar.  The download is available from the http://odata4j.org/ website.  Extract the zip.  The only jar you need for the purpose of this feature is odata4j-0.7.0-nojpabundle.jar.  Add the jar to your WEB-INF/lib and restart CFML.

Make the call

As I mentioned there is only one method in OData4J we are interested in for the purposes of parsing these complex filters.  The method is located here: org.odata4j.producer.resources.OptionsQueryParser#parseFilter() and only takes 1 string argument which is the complex filter used in the REST call.
So with that we can very easily make a call to this method.

createObject("java", "org.odata4j.producer.resources.OptionsQueryParser").parseFilter(javaCast("string", "myColumn eq 'abc'"))

One thing I will point out is that you cannot pass the parseFilter method an empty string.  It does not like this and will throw an error which isn't very informative.  Why they could not easily handle an empty value and just return null is beyond me.  Anyway, that stumped me for awhile so I'll save you the trouble - wrap this call in a condition checking the filter length first.

What do we get for our troubles?

The return we get for this call is not SQL - that would be too easy and make a very short write up.  What we get is a Java object of class EqExpression.  At this point of implementing anything new we would typically resort to documentation which is found here: http://odata4j.org/v/0.7/javadoc/.  Let me tell you these are the most useless docs I have read in a long time.  Yes all of the classes and methods link to each other which is great but it lacks any text explaining what anything does.  Completely useless!  So we resort to our second best method of exploring new libraries in ColdFusion... writeDump()!  And there was a lot of that going on to figure this one out.

This object has two methods that give us the data we need: getLHS() and getRHS().  I am guessing those mean LHS - left-hand side and RHS - right-hand side.  I could not find anything that explained what these meant.  Exploring the getLHS() method gives us a getPropertyName() method which returns us 'myColumn' and exploring the getRHS() method gives us a getValue() method which returns us 'abc'.  Cool, very simply we can use that to write our SQL.  But honestly, this wasn't very complex and if that's all you need out of your REST filters, you may as well just skip OData.  So let's put this to some good use.

createObject("java", "org.odata4j.producer.resources.OptionsQueryParser").parseFilter(javaCast("string", "myColumn eq 'abc' and myColumn2 ne 'xyz'"))

Okay this doesn't look too much harder than the last example. Well when you look at what you get back you will see just how wrong you are.  This time you get back a Java object of class AndExpression.  This object still has both getLHS() and and getRHS() but now they each return a EqExpression object.  Now if we added yet another 'and' to the above filter or used the 'substringof' in the filter, things will just keep getting more and more complicated.

What this level of complexity calls for is a recursive function.  This will allow the function to call itself whenever it encounters an object which contains other objects.  So let's jump into it.

var filter = "myColumn eq 'abc' and myColumn2 ne 'xyz'";
var parsedFilter = createObject("java", "org.odata4j.producer.resources.OptionsQueryParser").parseFilter(javaCast("string", filter));
var parsedSQL = expressionToSQL(parsedFilter);
variables.operatorsMap = {
 "EqExpression": "=",
 "NeExpression": "!=",
 "GtExpression": ">",
 "GeExpression": ">=",
 "LtExpression": "<",
 "LeExpression": "<=",
 "AndExpression": "AND",
 "OrExpression": "OR",
 "NotExpression": "NOT"

function expressionToSQL(required filter) {
 // this function does not handle everything yet:
 // works: and, or, eq, ne, lt, le, gt, ge, startswith, endswith, substringof
 // not working: paranthesis, not, arithmetic operators, no methods except noted above
 var sql = createObject("java", "java.lang.StringBuilder").init();
 var params = {};
 var type = filter.toString();

 if (listFind("EqExpression,NeExpression,GtExpression,GeExpression,LtExpression,LeExpression", type)) {
  if (filter.getLHS().toString() == "EntitySimpleProperty") {
   var columnName = filter.getLHS().getPropertyName();
   sql.append(columnName & variables.operatorsMap[type] & ":" & columnName);
   params[columnName] = filter.getRHS().getValue();
  else {
   // ideally you want to log a warning here and skip this object

   throw(message="Could not convert expression to SQL.", detail="Type '" & filter.getLHS().toString() & "' unaccounted for.");
 else if (listFind("StartsWithMethodCallExpression,EndsWithMethodCallExpression,SubstringOfMethodCallExpression", type)) {
  var columnName = filter.getTarget().getPropertyName();
  sql.append(columnName & " LIKE :" & columnName);
  if (type == "StartsWithMethodCallExpression") {
   // startswith converts to 'LIKE value%'
   params[columnName] = filter.getValue().getValue() & "%";
  else if (type == "EndsWithMethodCallExpression") {
   // endswith converts to 'LIKE %value'
   params[columnName] = "%" & filter.getValue().getValue();
  else if (type == "SubstringOfMethodCallExpression") {
   // substringof converts to 'LIKE %value%'
   params[columnName] = "%" & filter.getValue().getValue() & "%";
 else if (listFind("AndExpression,OrExpression", type)) {
  // recursively call method passing LHS object
  var lResult = expressionToSQL(filter.getLHS());
  // add returned SQL to our SQL
  // merge parameters together

  // recursively call method passing RHS object
  var rResult = expressionToSQL(filter.getRHS());
  // add returned SQL to our SQL with proper operator
  sql.append(" " & variables.operatorsMap[type] & " " & rResult.sql);
  // merge parameters together
 else {
  // ideally you want to log a warning here and skip this object
  throw(message="Could not convert expression to SQL.", detail="Type '" & type & "' unaccounted for.");

 return {
  "sql": sql.toString(),
  "parameters": params

Now as you can see from the comments in the function not all operators and methods available in OData are accounted for. For my purposes this function suits my needs. I am sure in the future I will want some if not all of the other features of the OData filter but for now this will do. This is also a great starting point for anyone needing this feature and you can build on this to finish out the other OData features.

You will also notice that I split the generated SQL from the values.  This makes it very easy to pass this onto your Query() object or in ORMExecuteQuery() while keeping SQL injection prevention in mind, especially with an interface as exposed to the outside world as a REST web service will be.  You may want to also add validation to this function, perhaps pass a second argument with a list of allowed columns to perform filtering on.  This serves two purposes 1) ensures no one can break the query by passing invalid columns and 2) restricts the columns that can be filtered in case you have some you do not want to be filtered on like large text.

So back to our original example.

https://mydomain.com/REST/People/?filter=startswith(firstName, 'd') and isActive eq 1

Passing through the above functions this will give you back the below:

With this return data, assuming we assigned the result to a variable named 'parsed', we can call ORMExecuteQuery("FROM People WHERE " & parsed.sql, parsed.parameters) and will give you a successful query run.

So that's it!  This should be enough to get you started on your way to more complex filters in REST web services.  If anyone gets additional operators, methods, or other parts of the conversion working and wants to share, please feel free.

UPDATE: This is now a project on GitHub available here: https://github.com/damonmiller/odata4cf.  Please check it out for the latest working version of this code.  Thanks!