Windows Azure Mobile Services is using SQL Azure as the backend, one of the benefits you have with a SQL database is the ability to join tables simply, but currently that ability is not manifested in an easy way through the server-side scripts or the client SDK of the mobile services.
So when I needed to get rows by joining 2 tables, I used this following simple solution.
In my case I had 2 tables, one for users with the user id and user name among other columns, and the other for user groups with information on which user is a part of which group, there I had the same user id with other group related information.
I needed a view with all of the user group columns together with the name of the user.
To do that I've created a new table called UserGroupsExtended and I've set permissions to admin for all operations except read, this is a workaround for creating a new endpoint in our mobile service.
In the read operation server-side script of this new table, I have the following code in which I use a SQL query to join the 2 tables and return the results of that query (instead of returning results related to the original UserGroupsExtended table which will always be empty).
| function read(query, user, request) { | |
| var sql = | |
| "select [user].name as userName, userGroup.groupId, userGroup.userId, userGroup.friendlyName, userGroup.id " + | |
| "from userGroup " + | |
| "inner join [user] on [user].userId = userGroup.userId " + | |
| "where userGroup.groupId in " + | |
| "(select groupId from userGroup where userId = ?) and userGroup.groupId = ?;"; | |
| mssql.query(sql, [user.userId, request.parameters.groupId], { | |
| success: function(results) { | |
| request.respond(statusCodes.OK, results); | |
| } | |
| }); | |
| } |
In this script I actually do more than that, I use a custom parameter (as described here) to pass the specific group id I want to get results on, this is since the query object is for the original UserGroupsExtended table which we are not really using.
And I also added this "in" part to only get results for groups where the current user is part of.
On the client side I've added a new UserGroupExtended class which derives from UserGroup and adds the user name as a property.
| [DataTable(Name = "userGroup")] | |
| public class UserGroup | |
| { | |
| [DataMember(Name = "id")] | |
| public int Id { get; set; } | |
| [DataMember(Name = "userId")] | |
| public string UserId { get; set; } | |
| [DataMember(Name = "groupId")] | |
| public string GroupId { get; set; } | |
| [DataMember(Name = "friendlyName")] | |
| public string FriendlyName { get; set; } | |
| } | |
| [DataTable(Name = "userGroupsExtended")] | |
| public class UserGroupExtended : UserGroup | |
| { | |
| [DataMember(Name = "userName")] | |
| public string UserName { get; set; } | |
| } |
And the last piece is for actually getting a result back from this new view.
| public class Data | |
| { | |
| public MobileServiceClient MobileService = new MobileServiceClient("", ""); | |
| private IMobileServiceTable<UserGroupExtended> userGroupExtendedTable; | |
| public async Task<IList<UserGroupExtended>> GetGroupUsers(string groupId) | |
| { | |
| return await this.MobileService.GetTable<UserGroupExtended>(new { groupId = groupId }).ToListAsync(); | |
| } | |
| } |
The last piece assumes you have the client-side code mentioned on the custom parameters post.