In the previous two blog posts, we have seen how the Core Data Services (CDS) client for XSJS XS Data Services (XSDS) allowed us to import metadata of a CDS data definition into XSJS and how entity instances could be created, retrieved, updated, and deleted. In this (final) block post in our series on XSDS, we present the unmanaged mode of XSDS, a JavaScript API for incrementally building queries against CDS entities, and execute them.
Incrementally Build and Execute Queries
Usually when you create database queries in XSJS, and in particular dynamic queries depending conditions like user provided parameters, you will have to deal with a String object containing the SQL query, and use String concatenation to assemble the query. A query builder provides a structured API instead of String operations, which can make your code a lot more succinct and readable. This is even more the case as in XSDS we can refer to CDS metadata, so the properties of the entities are already known.
In XSDS unmanaged mode we work with plain structured values retrieved from HANA by arbitrary queries. Unlike in managed mode, these general queries support all of the advanced HANA functionality for retrieving data.
After we have imported our entities (using the importEntities statement described in the first blog post), we can start to build a query.
A general query related to an entity is built by calling the $query() method of the entity constructor. Continuing the example from the previous blog posts, this most basic query looks as follows:
var qPosts = Post.$query();
The resulting object returned by $query() represents an initial query for all of the fields of the underlying entity, but without any associated entities.
Queries may be built from existing queries by chaining $where, $matching, $project, and further operators. With these operators, results can be constructed incrementally without accessing the database for intermediate results. A typical query construction could look as follows:
var qInterestingPosts = Post.$query().$where (/* condition */)
.$where (/* further condition */)
.$project (/* projection paths */)
.$limit(5); (/* 5 results only */)
The final query is executed by invoking its $execute() method:
var result = qInterestingPosts.$execute();
As in the managed case, the result of the query is a JavaScript object, but it is treated as a plain value, not an entity instance managed by XSDS. Unmanaged values may be converted to entity instances using the new operator (see the second post). Alternatively, the $find() or $findAll() methods return managed instances but only support a limited subset of the CDS query language.
Projections
The $project() method specifies the fields the query should return:
var qTitleAndComments = Post.$query().$project({
Title: true,
Comments: {
Author: {
Name: "CommentAuthorName"
},
Text: {
Text: true
}
}
});
var result = qTitleAndComments.$execute();
The list of projected fields is a JavaScript object, where desired fields are marked by either true or a String literal such as "CommentAuthorName" denoting an alias name. Above query may thus yield the result:
{
Title: "First Post!",
Comments: {
Author: {
CommentAuthorName: "Bob"
},
Text: {
Text: "Can we prevent this by software?"
}
}
}
Note that the actual database query automatically LEFT OUTER JOINs all required tables based on the associations involved. For above example, the generated SQL looks like (omitting the package prefix from the table name for readability):
SELECT "t0"."Title" AS "t0.Title",
"t0.Comments.Author"."Name" AS "t0.Comments.Author.Name",
"t0.Comments"."Text.Text" AS "t0.Comments.Text,Text"
FROM "bboard.post" "t0"
LEFT OUTER JOIN "bboard.comment" "t0.Comments"
ON "t0"."pid"="t0.Comments"."Post.pid"
LEFT OUTER JOIN "bboard.user" "t0.Comments.Author"
ON "t0.Comments"."Author.uid"="t0.Comments.Author"."uid"
LEFT OUTER JOIN "bboard.user" "t0.Author"
ON "t0"."Author.uid"="t0.Author"."uid"
Selections using $where
The $where() method filters the query result by some conditional expression. For example, to select all posts which were commented by a person with the same name as the post author, we write:
var qStrangePosts = qTitleAndComments.$where(Post.Comments.Author.Name.$eq(Post.Author.Name));
References to fields and associations such as Comments are available as properties of the entity constructor function, e.g., Post.Comments. As in the case with projections, XSDS generates all required JOINs for associations referenced by the conditions automatically, even if they are not part of the current projection.
To build complex query expressions, the XSDS expression language provides a number of predefined operators that work on all data types:
- $eq, $ne for SQL equality and inequality, resp.
- $gt, $lt, $gt, $le for the SQL operators >, <, <=, >=, resp.
- $null for the SQL operator IS NULL
- $like, $unlike for the SQL operators LIKE and
- $and, $or for SQL junctors AND and OR
A more complex selection statement could thus look like
Post.$query().$where(Post.Text.Lang.$eq("en").$and(Post.Rating.$lt(2).$or(Post.Rating.$gt(5))))
yielding a SQL query with the following where clause:
WHERE ("t0"."Text.Lang" = 'en') AND (("t0"."Rating" < 2) OR ("t0"."Rating" > 5))
For other SQL operators not part of the XSDS expression language you may use generic operators such as $prefixOp
qStrangePosts = qStrangePosts.$where(Post.Rating.$prefixOp("SQRT").$gt(1));
Selections using $matching
The $matching() method provides an alternative way to specify conditional expressions using the JSON-like syntax of $find() and $findAll() methods (see above).
var q1 = Posts.$query().$matching({ Rating: { $gt: 2 });
var q2 = Posts.$query().$matching({ Rating: { $ge: 1, $lt: 5 }, Parent: { $null: false } });
The main difference between $matching() and $findAll() is that the former returns an unmanaged, plain value and ignores all unpersisted changes to any entity instances.
We can think of the JSON-like conditional expression as a “template” that the result should match. Compared to the XSDS expression language used by $where(), the matching syntax is more concise, but also less expressive. Also note that the expression language does not apply to managed queries, e.g., to $find() or $findAll().
Calculated Fields and Aggregations
Arbitrary calculated values may be added to the result set by using the $addFields() method. As an example, we return the square root of the post’s rating as an additional field MyRating:
var qRootedRatings = Posts.$query().$addFields({ MyRating: Post.Rating.$prefixOp("SQRT") });
Aggregations are a special case of calculated fields that combine the $addFields operator with an additional $aggregate() method. For example, to retrieve the average rating per user, we would write:
var qUserRating = Post.$query().$aggregate({ Author: { uid: true, Name: true } })
.$addFields({ AverageRating: Post.Rating.$avg() });
In SQL terms, the $aggregate operator creates a GROUP BY expression for the specified paths and automatically projects the result on those. For an even more restrictive projection you may replace the true by a false in the $aggregate call:
var qUserRating = Post.$query().$aggregate({ Author: { uid: false, Name: true } })
.$addFields({ AverageRating: Post.Rating.$avg() });
This will remove the users’ IDs from the result set.
Currently, XSDS supports aggregation operators $avg, $sum, $count, $max, and $min.
Ordering, Size Limits, and Duplicate Elimination
The order of the result set is defined by the $order() method. Each order criteria contains a property by with an expression according which to order. Optionally each criterion can contain a flag desc to require a descending order and a nullsLast flag. The following example uses two criteria to first order descending by rating and then order ascending by author name:
var qOrderedPosts = Post.$query().$order({ $by: Post.Rating, $desc: true }, { $by: Post.Author.Name });
The $distinct operator removes duplicates from the result set. To get the set of all ratings we can write:
var qAllRatings = Post.$query().$project({ Rating: true }).$distinct();
The number of records returned may be specified by using the $limit operator, which introduces the LIMIT and OFFSET keywords into the SQL query:
var qNextFivePosts = qStrangePosts.$limit(5, 3); //skip posts 1-3, return posts 4-8
Conclusion
In this blog post you have seen how you can exploit the full power of HANA queries against CDS entities through a convenient JavaScript API from XSJS. The unmanaged mode presented here complements the managed mode presented in the previous post.
Which mode you should use depends very much on your use case, if you have simple queries and need to modify objects, probably the instance management is valuable to you; if you need to create complex dynamic queries, you may want to work in the unmanaged mode. In any case you can easily switch between the modes, as described in the last section of the previous post.
We hope you enjoy writing your native HANA applications using XSDS, giving you a seamless experience in consuming database artifacts in the XSJS layer.
