Blog
All Blog Posts | Next Post | Previous Post
Querying objects in your REST server with XData
Wednesday, August 12, 2015
One of my favourite features of TMS XData is its powerful querying capabilities. With no extra line of code in your server, clients can choose how to retrieve the server objects, filter them, set an order, etc..For example, you can perform a GET request to get Customer objects but filter only those which name is John:
http://server:2001/tms/xdata/Customer?$filter=Name eq 'John'
You can also filter by properties of associated objects, at any level. The next example gets all customers which associated country name is "USA":
http://server:2001/tms/xdata/Customer?$filter=Country/Name eq 'USA'
You can use order, top and skip to get paged results (get next 20 orders from 41st one):
http://server:2001/tms/xdata/Order?$orderby=Date&$top=20&$skip=40
And, of course, you can combine all those with parenthesis and relational operators to build really complex queries:
http://server:2001/tms/xdata/Customer?$filter=(Name eq 'John' or Name eq 'Jack') and Country/Name eq 'USA'
But the purpose of this post is to tell about the new built-in functions introduced in version 1.5. They increase flexibility of the queries even more. Functions that extract part of of a date/time property were added, so now you can for example filter invoices for year 2014:
http://server:2001/tms/xdata/Invoice?$filter=year(IssueDate) eq 2014
There are of course similar functions to extract Month, Day, Hour, Minute and Second.
String-related functions were also added. Upper and Lower were introduced:
http://server:2001/tms/xdata/Customer?$filter=lower(Name) eq 'paul'
You can also use length function to retrieve the length of a string in characters:
http://server:2001/tms/xdata/Customer?$filter=length(Name) eq 15
And Position and Substring functions can also be used to retrieve the position of a substring in a string (Position) or to retrieve part of a string (Substring). They would be the equivalent of Delphi functions Pos and Copy, respectively:
http://server:2001/tms/xdata/Customer?$filter=substring(CompanyName, 2, 4) eq 'oogl'
http://server:2001/tms/xdata/Customer?$filter=position('jr', Name) gt 0And it's worth to remember that having TMS Aurelius working behind the scenes, all those queries will work the same way regardless of the database your are using at the server side, be it MS SQL Server, MySQL, Oracle, or any supported database you want. Best part is that it's not going to stop here, stay tuned for future releases that will bring even more querying features!
Wagner Landgraf
This blog post has not received any comments yet.
All Blog Posts | Next Post | Previous Post