Playing with the query object in Read operations on Azure Mobile Services

  • Article

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.

Comments

  • Anonymous
    September 20, 2012
    Good Article. Kepp up the good work.
  • Anonymous
    September 28, 2012
    thanks for the Article! Can you give one example how can be made?Client read from one table (t1), there is one script on READ. Idea of this script not put data not from this table (t1), put from another table (t2) by SQL query (select * from t2 order by ....)?
  • Anonymous
    September 28, 2012
    Hi Andrey,You can always bypass completely the query object and do the querying yourself. In this case, if you have a SQL statement you want to use, you can use the mssql object (msdn.microsoft.com/.../jj554212.aspx), something similar to the code below:function read(query, user, request) {   mssql.query('select * from t2 order by name', [], {       success: function(results) {           request.respond(statusCodes.OK, results);       }   });}Another alternative is to use the tables object (msdn.microsoft.com/.../jj614364.aspx) to get a reference to the table t2, and read it from there:function read(query, user, request) {   var t2 = tables.getTable('t2');   t2.orderBy('Name').read({       success: function(results) {           request.respond(statusCodes.OK, results);       }   });}
  • Anonymous
    September 28, 2012
    first example doesn't work, raise error:Error in 'read' script for table 't1'. TypeError: Object [object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object],[object Object] has no method 'respond'   at <t1.read.js>:8:29   [external code]
  • Anonymous
    September 29, 2012
    Are you sure you're not calling 'respond' on the results object? It needs to be called on the request object.
  • Anonymous
    June 29, 2014
    The comment has been removed
  • Anonymous
    June 29, 2014
    This page helps me solve my problem : blogs.msdn.com/.../enabling-paging-with-total-count-for-tables-with-custom-read-scripts-in-the-node-js-runtime.aspxI can now query directly in SQL (using mssql object) and still have pagination working. Thanks.
  • Anonymous
    July 02, 2014
    Cool, glad to see you got it working!
  • Anonymous
    July 03, 2014
    Your blogpost have been really helpful in the process of developing a real-life application with Azure.If you have some time I still have a little thing that bugs me, how could I return data after inserting something with the mssql object (so that I could use them in the success callback) ?snippet :var insertQuery = "INSERT INTO table( Columns) VALUES ( 'Values' )";mssql.query(insertQuery, {   success: function(results) {       console.log(results);                     }});Here results is empty. I tried to use the OUTPUT clause in my query, but i get the following error :Error occurred executing query: Error: [Microsoft][SQL Server Native Client 10.0][SQL Server]The target table 'table' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause.Is there a better way to do this that i'm missing ?
  • Anonymous
    July 06, 2014
    For those who might be interested heres my workaround, i don't really know if this is the best way to go :var insertQuery = "DECLARE @T TABLE ( lastInsertedId NVARCHAR(50) );";           insertQuery = insertQuery + "INSERT INTO table( column ) OUTPUT INSERTED.id INTO @T VALUES ( 'value' );";           insertQuery = insertQuery + "SELECT * FROM @T&quot;;           var sqlResponseCount = 0;             mssql.query(insertQuery, {               success: function(results) {                   if (sqlResponseCount++ == 1) {                       item.id = results[0].lastInsertedId;                                     request.respond(201, item);                   }               }           });
  • Anonymous
    July 06, 2014
    Hi Paul, yours is indeed the way that the mobile services runtime uses internally. You can even see it at the runtime code itself (function _buildSqlInsert at documented.azure-mobile.net/.../fs, from a mobile service which displays the internal files - weblogs.asp.net/.../azure-mobile-services-revealing-the-internals)
  • Anonymous
    October 11, 2014
    How would I go about pulling a value off the query object? The query object has a parameter: accountId and I can see the value when I stringify the query object but I can't figure out how to just grab the value for accountId?  Any help would be appreciated!
  • Anonymous
    October 14, 2014
    The comment has been removed
  • Anonymous
    December 17, 2014
    Paul, I just find this page and your comment. I'm doing something similar to you but my select after the update is always empty (see stackoverflow.com/.../azure-mobile-services-custom-api-update-ouput-followed-by-select-returns-empty). Do you remember doing something else that what you mentionned here?Tks.