Class: Query

.xsds. Query

new Query()

Methods

$addFields(newFields) → {sap.hana.xs.libs.dbutils.xsds.Query}

Returns a new query which is equivalent to the called query, but with additional calculated fields in the projection clause as specified in newFields.
Parameters:
Name Type Description
newFields object An object {p_0: expr_0, ... p_n: expr_n}. Each property p: expr of newFields results in one new field p added to the projection clause of the query. The value expr must be an expression as specified in the description of the $where clause.
Returns:
a new query with the specified field added to the projection
Type
sap.hana.xs.libs.dbutils.xsds.Query
Example
result = SOHeader.$query().$limit(10).$project({
   SALESORDERID: true,
   NETAMOUNT: "TotalNet",
   items: {
       NETAMOUNT: true
   }
}).$addFields({
   "DaysAgo": soHeader.items.DELIVERYDATE.$prefixOp("DAYS_BETWEEN", new Date())
}).$execute();

// example result:
// [{
//    SALESORDERID: "0500000486",
//    TotalNet: "273.9",
//    items: {
//        NETAMOUNT: "87"
//    },
//    DaysAgo: 681
// }]

$aggregate(pathSpec) → {sap.hana.xs.libs.dbutils.xsds.Query}

Returns a new query which is equivalent to the called query, but with an additional GROUP BY section and a restricted projection clause based on the pathSpec object.
Parameters:
Name Type Description
pathSpec object A path expression as described in the desciption of the $project operator. pathSpec will lead to the GROUP BY clause of the generated query. In addition, the projection is overridden with the same clause. It is possible to hide some parts of the pathSpec, so that they appear in the GROUP BY clause but not in the projection: for this, the pathSpec property needs be terminated with the constant false (instead of true or a String literal).
Returns:
a new query which performs the aggregation
Type
sap.hana.xs.libs.dbutils.xsds.Query
Example
var averageQuantity = soItem.$query().$limit(2).$aggregate({
    SALESORDERID: true,
    PRODUCTID: true
}).$addFields({
    averageQuantity: soItem.QUANTITY.$avg()
}).$execute();

// example result:
// [{
//     PRODUCTID: "HT-1107",
//     averageQuantity: "1"
// },
// {
//     PRODUCTID: "HT-1072",
//     averageQuantity: "3"
// }]

$defaultProject() → {sap.hana.xs.libs.dbutils.xsds.Query}

Returns a new query which is equivalent to the called query, but replaces any projection specification by the default projection. The default projection specifies all fields of the entitiy without following associations.
Returns:
a new query with the default projection specification added to the query
Type
sap.hana.xs.libs.dbutils.xsds.Query
Example
soHeader.$query().$defaultProject();
// example result:
//       {
//           DELIVERYSTATUS: "I",
//           BILLINGSTATUS: "I",
//           LIFECYCLESTATUS: "X",
//           TAXAMOUNT: "52.04",
//           TotalNet: "273.9",
//           GROSSAMOUNT: "325.94",
//           CURRENCY: "EUR",
//           PARTNERID: "0100000044",
//           HISTORY: {
//               CHANGEDAT: "2012-05-13T22:00:00.000Z",
//               CHANGEDBY: "0000000033",
//               CREATEDAT: "2012-04-29T22:00:00.000Z",
//               CREATEDBY: "0000000033"
//           },
//           SALESORDERID: "0500000486"
//       }

$execute(config)

Executes the query and transforms the result back into the structure imposed by the CDS/XSDS metadata.
Parameters:
Name Type Argument Description
config Object <optional>
 specifies the appearance of the result set. If this value is not set or empty, a structured result set is returned, i.e. the result set is delivered as an array or array-like object of rows, each of which contains an object. That object contains the local fields of the entity and as subobjects the requested values by association.
Properties
Name Type Argument Description
$flat boolean <optional>
 if true, returns the data as delivered by the database as an array or array-like object of rows, each of which contains an object with the field (column) names as properties
$factorized boolean <optional>
 if true, summarizes the result set based on the object's key(s).
Example: [{id: 1, 1_m_association: 0}, {id: 1, 1_m_association: 1}] -> [{id: 1, 1_m_association: [0, 1]}]
Example
var result = SOHeader.$query().$project({SALESORDERID: true}).$execute();
    // example result:
    //  [{
    //    "SALESORDERID": "0500000486",
    //    "items": {
    //        "NETAMOUNT": "87"
    //    }
    //   }, {
    //    "SALESORDERID": "0500000486",
    //    "items": {
    //        "NETAMOUNT": "29.9"
    //    }
    //   }]

    result = SOHeader.$query().$project({SALESORDERID: true}).$execute({$factorized: true});
    // example result:
    // [{"SALESORDERID":"0500000486",
    //      "items":[
    //           {"NETAMOUNT":"87"},
    //           {"NETAMOUNT":"29.9"}
    //      ]
    // }]

    result = SOHeader.$query().$project({SALESORDERID: true}).$execute({$flat: true});
    // example result:
    //  [{
    //    "SALESORDERID": "0500000486",
    //    "items.NETAMOUNT": "87"
    //   },
    //   {
    //    "SALESORDERID": "0500000486",
    //    "items.NETAMOUNT": "29.9"
    //   }
    //]
    //

$limit(n, m) → {sap.hana.xs.libs.dbutils.xsds.Query}

Returns a new query which is equivalent to the called query, but includes a LIMIT n, m specification, i.e. will return the first n result records, and skip m results
Parameters:
Name Type Argument Description
n number the number of result set elements which should be returned
m number <nullable>
 the offset i.e. the number of skipped rows
Returns:
a new query with the limit specification added
Type
sap.hana.xs.libs.dbutils.xsds.Query
Example
soHeader.$query().$limit(10, 5).$execute();

$matching(template) → {sap.hana.xs.libs.dbutils.xsds.Query}

Returns a new query which is equivalent to the called query, but includes a selection condition which is equivalent to the template specified as argument.
Parameters:
Name Type Description
template is an object following the result set structure together with values specifying the allowed values of the result set. For a complete account of the syntax of template, see {{sap.hana.xs.libs.dbutils.xsds.Entity#$find}.
Returns:
a new query with the selection specification added to the query
Type
sap.hana.xs.libs.dbutils.xsds.Query
Example
qSelectedOrders = soHeader.$query().$limit(1).$project({
         SALESORDERID: true,
         NETAMOUNT: "TotalNet",
         items: {
             NETAMOUNT: true
         }
     }).$matching({
         items: {
             CURRENCY: 'EUR',
             QUANTITY: {
                 $gt: 2
             }
         }
     });

// example result: 
// [{
//       SALESORDERID: "0500000236",
//       TotalNet: "273.9",
//       items: {
//           NETAMOUNT: "87"
//       }
// }]

$order(criterion_i:) → {sap.hana.xs.libs.dbutils.xsds.Query}

Returns a new query which is equivalent to the called query, but with an additional ordering specification. The order is specified using a number of criteria passed to the operator as arguments.
Parameters:
Name Type Description
criterion_i: object An object {$by: [, $desc: true|false] [, $nullsLast: true|false]}, where $by specifies an expression (following the syntax of $where) or is a column number. If $desc is set to true the order of the returned result set is in descending order, otherwise it is in ascending order. If $nullsLast is set to true, null values are at the end of the result set, if it is false they are at the beginning. If $nullsLast is not set, the default of SQL is taken (depending on ascending/descending order).
Returns:
a new query which performs the aggregation
Type
sap.hana.xs.libs.dbutils.xsds.Query
Example
qSelectedOrders = soHeader.$query().$limit(2).$project({
     SALESORDERID: true,
     NETAMOUNT: "TotalNet",
     items: {
         NETAMOUNT: true
     }
 }).$order({
     $by: soHeader.NETAMOUNT,
     $desc: true
 }, {
     $by: soHeader.items.NETAMOUNT
 }).$execute();

$project(pathSpec) → {sap.hana.xs.libs.dbutils.xsds.Query}

Returns a new query which is equivalent to the called query, but which contains only those column values reachable through the specified path specification. The projection ignores (i.e. overrides) the projection in the original query.
Parameters:
Name Type Description
pathSpec object a JavaScript object which follows in its structure the expected result object structure. A navigation a.b.c following associations a and b and referring to a field c is represented by a JavaScript object {a: {b: {c: true}}}. The value true can also be any string expression, which results in an alias in the result set. It is also possible to merge several path expressions into one path specification, such as {a: {b1: {c1: true}, b2: {c2: true}}} corresponding to the path expressions a.b1.c1 and a.b2.c2.
Returns:
a new query with the projection specification added to the query
Type
sap.hana.xs.libs.dbutils.xsds.Query
Example
var orderAndItemTitles = SOHeader.$query().$project({
             SALESORDERID: true,
             NETAMOUNT: "TotalNet",
             items: {
                 NETAMOUNT: true
             }
         }).$execute();
   // example result:
   //   [{   "SALESORDERID": "0500000236",
   //        "TotalNet": 273.9,
   //        "items": {
   //             "NETAMOUNT": 29.9
   //         }
   //    }]

$sql() → {string}

returns the SQL string represented by the query object.
Returns:
the query string represented by the query object
Type
string

$where(cond) → {sap.hana.xs.libs.dbutils.xsds.Query}

Returns a new query which is equivalent to the called query, but includes a selection condition cond specified using an expression API.
Parameters:
Name Type Description
cond A Boolean expression specified as follows: An expression is either
  • a column reference constructed using e.f where e is an entity and f is a field of e as defined in the entity definition.
  • a column reference following an association path using e.assoc1,assoc2....f.
  • a function application to an expression e, for instance e.$plus(5). All functions which can be applied are specified in the {@linkn sap.hana.xs.libs.dbutils.xsds.Expr}
  • a Javascript literal
A Boolean expression is either
  • e.$eq(e0), if e and e0 are expressions (representing the SQL equality operator), similar for the operators $gt, $lt, $ge, $le, $ne representing the SQL operators
  • e.$and(e0) if e and e0 are Boolean expressions (representing the SQL AND operator)
  • e.$or(e0) if e and e0 are Boolean expressions (representing the SQL OR operator)
  • e.$null (representing the SQL IS NULL operator)
  • e.$infixOp(sqlop, e0) if e and e0 are expressions and sqlop is a SQL infix operator.
  • e.$prefixOp(sqlOp, e0,...,en) if e, e0,...,en are expressions and sqlop is a SQL prefix operator
For a full overview on all possible operators constructing a boolean expression, see sap.hana.xs.libs.dbutils.xsds.Expr Note, that due to JavaScript pecularities, the association or property names "name", "prototype", "length", "caller" need to be escaped by a $ sign. So in order to refer to an entity property "name" in E, you need to write E.$name
Returns:
a new query with the selection specification added to the query
Type
sap.hana.xs.libs.dbutils.xsds.Query
Example
var qSelectedOrders = soHeader.$query().$project({
     SALESORDERID: true,
     NETAMOUNT: "TotalNet",
     items: {
         NETAMOUNT: true
     }
 }).$where(soHeader.items.NETAMOUNT.$div(soHeader.NETAMOUNT).$gt(0.5))

// example result:
//   [{
//           SALESORDERID: "0500001005",
//           TotalNet: "273.9",
//           items: {
//               NETAMOUNT: "780"
//       }]