As I was writing the last post (about storing per-user data in Azure Mobile Services), I used the where method in the Query object (passed to the read function) to filter the results only to those for the logged in user.

  1. function read(query, user, request) {
  2.     query.where({ UserId: user.userId });
  3.     request.execute();
  4. }

I did that following the example in the "Authorize Users in Scripts” tutorial in the Azure web page. But later on I started looking more into the documentation of the Query object, and noticed that there are other ways to do the same thing, so I decided to explore them, and found them interesting enough for a blog post.

It’s all additive

Before we start, let’s look at exactly what the first line in the “read” function means:

  1. query.where({ UserId: user.userId });

By adding calling “where” on the Query object and passing an object as a parameter, we’re telling it to filter the results to those whose UserId column contain the value passed to it (user.userId). Also, this filter is applied in addition to whatever filter the client request had, essentially adding an AND expression in the end of the WHERE clause which is passed to the database when retrieving the data (the runtime could simply pass the original request and later filter based on that object, but that would be woefully inefficient). Also notice that by doing that we’re modifying the query object itself. For example, if this request arrived for the read operation in our table:

GET /tables/MovieRating?$filter=(Rating+gt+2) HTTP/1.1

The request would normally be translated roughly into the following SQL query

SELECT *
FROM MovieRating
WHERE Rating > 2

But once the where method is called on the query object, the query translation becomes something along the lines of

SELECT *
FROM MovieRating
WHERE Rating > 2 AND UserId = ?

Where the ‘?’ is the parameter which is passed to the query.

Now, that object which is passed to the where method can have an arbitrary number of parameters, and they’ll all be interpreted as AND clauses to the query. For example, the line below

  1. query.where({ UserId: user.userId, Category: "Fiction", Studio: "Paramount" });

Would be roughly translated (for the same request shown before) as

SELECT *
FROM MovieRating
WHERE Rating > 2 AND UserId = ?
      AND Category = ‘Fiction’
      AND Studio = ‘Paramount’

So that’s the simple way to filter based on some parameters. There is, however, another way to write the same query. As I mentioned, a .where call on the Query object will add an AND expression to the WHERE clause, so we could have written that in three lines instead:

  1. query.where({ UserId: user.userId });
  2. query.where({ Category: "Fiction" });
  3. query.where({ Studio: "Paramount" });

Or using the fluent API:

  1. query.where({ UserId: user.userId })
  2.      .where({ Category: "Fiction" })
  3.      .where({ Studio: "Paramount" });

But we’re not really limited to where clauses; as the documentation of the Query object shows, we can affect other parts of the query as well. And again, it’s all additive, so we can just keep calling them and we’ll each time affect more of the query. One more example:

  1. function read(query, user, request) {
  2.     query.where({ UserId: user.userId })
  3.          .select('id', 'MovieName', 'MovieRating')
  4.          .orderBy('MovieName')
  5.          .take(10);
  6.     request.execute();
  7. }

This will be roughly translated to the following clause:

SELECT TOP 10 [id], [MovieName], [MovieRating]
FROM MovieRating
WHERE Rating > 2 AND UserId = ?
ORDER BY MovieName

So that’s the simple usage of the Query object.

Complex filtering

So far all the where calls we made to the query object took an object with some parameters, and we compare them with the data from the database. But all the comparison we can make with that overload are all equality. We cannot, passing an object to the where method of the Query object,, say that we want to filter all movies whose rating is less than 5, for example. There is, however, another overload for the query method, which takes a function instead of the object. In this function we can then write more complex expressions, using operators such as inequality and other relational operations.

There is a big caveat in this method, which wasn’t clear for me at first. The body of the function which is passed to the where method is never executed. Instead, just like with the “template object” method, its expression is translated into a SQL expression which will be sent to the backend database. That makes sense, since we want to filter directly at the source instead of returning several rows, many of which will be discarded (notice that you can still do that, as I’ll show later on). But the function must consist of a single “return” statement with a supported expression. If, for example, you try to write the function below:

  1. query.where(function () {
  2.     var i = 1;
  3.     return this.Rating > i;
  4. });

You’d get a 500 response to requests, with the following entry in the mobile service logs:

Error in 'read' script for table 'MovieRating'. Expected a predicate with a single return statement, not function () { var i = 1; return this.Rating > i; }

So not all functions can be used. In order to be used as the parameter of a query method, a function must follow those rules:

  • Have only one return statement, as mentioned before
  • The row being filtered is represented by the this object in the function
  • Any values outside the function must be passed to it as parameters (no closures)

The last rule originally threw me off. When I tried writing the user filtering code, I wrote the read function below:

  1. function read(query, user, request) {
  2.     query.where(function () {
  3.         return this.UserId == user.userId;
  4.     });
  5.     request.execute();
  6. }

But when trying to run it, the request returned a 500, and the logs complained about a “missing parameter”. This is how it should be written:

  1. function read(query, user, request) {
  2.     query.where(function (user) {
  3.         return this.UserId == user.userId;
  4.     }, user);
  5.     request.execute();
  6. }

Or by passing the user id directly:

  1. function read(query, user, request) {
  2.     query.where(function (userId) {
  3.         return this.UserId == userId;
  4.     }, user.userId);
  5.     request.execute();
  6. }

So, as long as we follow the rules, we can add more complex filters to our database queries. For example, the query below returns the ratings for the logged in user, for fiction or action movies, whose ratings is greater than 2.

  1. function read(query, user, request) {
  2.     query.where(function (userId) {
  3.         return this.UserId == userId &&
  4.               (this.Category == "Fiction" || this.Category == "Action") &&
  5.                this.Rating > 2;
  6.     }, user.userId);
  7.     request.execute();
  8. }

And that’s the option for complex queries.

Post-query filtering

I mentioned that calling the where method on the Query object affects the actual query which will be sent to the backend database. But there are some scenarios where a post-retrieval filtering may be useful: some additional data may be required (such as data from other table), some expression may not be supported to be translated into a SQL WHERE clause, among others. In those cases, we can pass a parameter to the execute method of the Request object, and define a “success” member whose value is a function which will receive the results of the query. At that point, we’re free to execute any JavaScript (node.js) code which we want – the only thing that we need to do is to call request.respond to signal the runtime that the response is ready to be returned. In the example below, we’re filtering the ratings for the movies from the 1980’s using a regular expression (which is not supported on pre-query filtering.

  1. function read(query, user, request) {
  2.     query.where(function (userId) {
  3.         return this.UserId == userId;
  4.     }, user.userId);
  5.     request.execute({
  6.         success: function (results) {
  7.             var regex = /\(198\d\)/;
  8.             var filtered = results.filter(function(item) {
  9.                 return regex.test(item.MovieName);
  10.             });
  11.  
  12.             request.respond(statusCodes.OK, filtered);
  13.         }
  14.     });
  15. }

Another way to implement the same method above would be to call read directly on the Query object (instead of request.execute):

  1. function read(query, user, request) {
  2.     query.where(function (userId) {
  3.         return this.UserId == userId;
  4.     }, user.userId).read({
  5.         success: function (results) {
  6.             var regex = /\(198\d\)/;
  7.             var filtered = results.filter(function(item) {
  8.                 return regex.test(item.MovieName);
  9.             });
  10.  
  11.             request.respond(statusCodes.OK, filtered);
  12.         }
  13.     });
  14. }

The two implementations above are equivalent.

Bypassing the query object

Just a little not for completeness sake. The read scripts receive the Query object as a parameter, but it doesn’t necessarily need to use it. It’s actually possible to completely ignore the query object (and even the backing database), as we can respond to the request directly. The script below does exactly that.

  1. function read(query, user, request) {
  2.     request.respond(statusCodes.OK, [
  3.         { id: 1, name: 'Scooby', age: 10 },
  4.         { id: 2, name: 'Shaggy', age: 19 },
  5.         { id: 3, name: 'Velma', age: 20 }
  6.     ]);
  7. }

Also, the response doesn’t even need to be an array, any valid JSON object would also be a valid response to the operation.

Wrapping up

In this post I showed some ways of interacting with the query object to customize the data retrieval in read operations. I hope that this helped you understand how the read scripts work, so if you need to customize one, those examples can help you with that.