Search

Constructing Queries with cds.ql

Use the cds.ql API to construct queries as first-order CQN objects using a fluent, embedded SQL-like API style. These queries are usually passed to services for execution using srv.run. These queries can be sent to services using the srv.run as shown in this example:

let foos = SELECT.from('Foo')
let bars = SELECT.from(foos).where({kind:'bar'})
const all = await cds.read (bars)

Note: While the API resembles well-known SQL constructs, cds.ql isn’t locked in to SQL. It can be used to capture any kind of queries as well as applied to any kind of services, including NoSQL or remote services.


SELECT .from …

SELECT .from (entity, key?, projection?) …

SELECT.from ('Books').where ({author:11})
SELECT.from (Books,[
  'ID', 'title', 'author.name as name'
]).where ({stock:{'>':11}})

The first argument is either a string of an entity or an entity definition as obtained from a data source’s .entities method.

The optional second argument is the same as in .columns.

.columns (projection | …columns)

The arguments are column expressions, which can be one of….

  • Standard SQL column expression strings, for example, name, x as y
  • Simple expression objects, for example, {x:'y'}
  • CQN expression objects, for example, {ref:['name']}, {ref:['x'], as:'y'}

Instead of passing in an arguments list, you can alternatively pass in a single array with all columns.

If the SELECT query contains columns or column aliases with the same name, the query is rejected. Example: SELECT.from(Foo).columns(['cols1', 'cols1']). Please make sure to use alias names that are unique within the SELECT.

The columns are additive when calling .columns repeatedly, for example SELECT.from(Foo,cols1) .columns(cols2) .columns(cols3)...

.where / having / groupBy / orderBy (expr)

These methods allow to fill in the respective standard SQL or CQL clauses. The arguments form expressions as explained in section Fluent Expression below.

.limit (rows, offset)

Equivalent of the standard SQL limit clause.

.forUpdate ({wait?}) …

The argument of this method is optional. Currently it can include the following option:

  • wait is an integer value, that specifies when to return an error if a lock can’t be obtained on a record. If this option isn’t specified, then it results in database-specific default behavior.

Records are locked, until the transaction has ended (commit or rollback).

const lock = await SELECT.from(Authors,ID).forUpdate()
const lock = await SELECT.from(Authors,ID).forUpdate({ wait:10 })

SELECT (…columns) .from (entity) …

This is a syntactical sugar variant to SELECT.from(entity) .columns(...), closer to and hence a reminiscence to standard SQL. The arguments are the same as in .columns

SELECT .one

This is a variant of SELECT.from that captures we’re interested in only the first entry. When executed, a single entry is returned instead of a result set, which means the following are equivalent:

const one = await SELECT.one(Authors).where({ID:111})
const all = await SELECT.from(Authors).where({ID:111})
const one = all[0]

SELECT .distinct

The equivalent of SQL’s SELECT DISTINCT ... from entity.




INSERT .into …

INSERT .into (entity, key?) …

Constructs an INSERT statement. In addition to the standard , it provides the variants .rows and .entries, which can automatically be mapped to prepared statements and hence processed more efficiently.

.columns (…)

Specifies the columns for which values are provided in a subsequently specified .rows or .values. Find examples below.

.values (…)

Specifies the values, which positionally match to specified .columns. Both, .columns and .values can alternatively wrapped into an array. The following examples are equivalent:

INSERT.into (Books) .columns (
  'ID', 'title', 'author_id', 'stock'
) .values (
  201, 'Wuthering Heights', 101, 12
)
INSERT.into (Books) .columns ([
  'ID', 'title', 'author_id', 'stock'
]) .values ([
  201, 'Wuthering Heights', 101, 12
])

.rows (…)

Allows inserting multiple rows with one statement where each row is an array of values with positional match to specified .columns, for example, as could be read from a CSV source.

INSERT.into (Books) .columns (
  'ID', 'title', 'author_id', 'stock'
) .rows (
  [ 201, 'Wuthering Heights', 101, 12 ],
  [ 251, 'The Raven', 150, 333 ],
  [ 252, 'Eleonora', 150, 234 ]
)

.entries (…)

Allows inserting multiple rows with one statement where each row is a record with named values, for example, as could be read from a JSON source.

INSERT.into (Books) .entries (
  { ID:201, title:'Wuthering Heights', author_id:101, stock:12 },
  { ID:251, title:'The Raven', author_id:150, stock:333 },
  { ID:271, title:'Catweazle', author_id:170, stock:222 }
)

.as (query)

Constructs a INSERT into SELECT statement.

INSERT.into('Bar') .as (SELECT.from('Foo'))

INSERT (…entries) .into (entity) …

This is just a syntactical sugar variant to INSERT.into(...).entries(...).




UPDATE

UPDATE (entity, key?) …

UPDATE('Books') .set ({
  stock:{'-=':1},
  lastChanged:Date.now()
}) .where ({ID:01})

.with, set (…)

Specifies the key-value pairs, with keys being element names of the target entity and values being expressions

.where (expr)

Allows add standard SQL WHERE clauses. The arguments form expressions as explained in section Fluent Expression below.




DELETE .from …

DELETE .from (entity, key?) …

DELETE.from('Books').where ({stock:{'<':1}})

.where (expr)

Allows add standard SQL WHERE clauses. The arguments form expressions as explained in section Fluent Expression below.




Expressions

There are two primary variants to specify expressions, for example, in .where or .orderBy clauses.

As alternating string / value lists

The arguments are an alternating list of (native) query fragments and values or CQN expression objects as in these examples:

SELECT.from('Books').where (
  `author.name in`, SELECT('name').from(Authors)
)
SELECT.from('Foo').where (`name like`, '%foo%', `and (
  ratio between`, 0.1, `and`, 0.3,
  `or ratio >`, 0.9,
`)`)

This variant gives maximum flexibility in constructing expressions, including using native operations, which aren’t even understood by CDS itself. Still, it ensured that all values are passed through the database drivers’ argument bindings, hence avoiding SQL injection.

As query-by-example objects

… with keys being references to elements and values being either literals or CQN expression objects as in these examples:

SELECT.from('Foo').where ({ name: {like: '%foo%'}, {or:{
  ratio: {between: 0.1, and: 0.3}, or: {ratio:{'>': 0.9}}
}})