Emulating Calculated Sub-Queries in Windows Azure Mobile Services
I've seen several posts on the web about needing an easy way to return calculated information from an Azure Mobile Service, without incurring costly API calls. I don't remember where I found this solution, but I didn't come up with it (if you did and leave a link in the comments, I'll update this post). It works like a charm tho, so here it goes.
SQL Computed Columns cannot execute queries themselves. But computed columns CAN use SQL User-Defined Functions, which in turn CAN use queries. So the trick is really straightforward: create a UDF that contains the query you'd like, and then add the function to your table specification.
Let's say you want to build an app that rates pictures. Do you have a Pictures table and a PictureRatings table. For the sake of simplicity, the pictures table has an ID column, and the PictureRatings table has an ID, a PictureID, and a Rating.
So first let's create the User-Defined Function:
This function returns a decimal in the format X.XX, so that your client-side code can render the rating in partial stars. Next, let's modify the table definition to leverage this UDF.
Now, whenever you query the table, you'll get the data back automagically, as if it were any other column, and you just saved yourself an API call. Now, it may not be the absolute best idea for SQL performance if either table gets huge, so make sure you are limiting the calls somehow (logged-in user / items per page, etc). But then again, are you really using Mobile Services / node.js for its amazing database perf? O_o
Now, we have one more thing we need to do. because computer columns cannot be inserted into, we need to make sure Mobile Services doesn't get it's pretty little head confused by the data. So you need to remove the average_rating from the incoming requests that modify the data, namely Inserts and Updates. An example of the Insert script is below, but the Update script is basically identical (that is, unless you're doing other fun things with the indert, like duplicate checking).
Thare you have it! A relatively simple trick that is easy to implement and replicate across your design as-needed.