CAPire

Appearance

On this page

Query Notation (CQN) ​

CQN is a canonical plain object representation of CDS queries. Such query objects can be obtained by parsing CQL, by using the query builder APIs, or by simply constructing respective objects directly in your code.

Examples ​

The following three snippets all construct the same query object:

js
// Parsing CQL
let query = cds.parse.cql (`SELECT from Foo`)
// Parsing CQL
let query = cds.parse.cql (`SELECT from Foo`)
js
// Query building
let query = SELECT.from('Foo')
// Query building
let query = SELECT.from('Foo')
js
// Constructing CQN objects in your code
let query = {SELECT:{from:[{ref:['Foo']}]}}
// Constructing CQN objects in your code
let query = {SELECT:{from:[{ref:['Foo']}]}}

That object can be executed with cds.run:

js
cds.run (query)
cds.run (query)

❗ Warning

Because of SQL injection, it's strongly discouraged to use cds.parse.cql in your request handlers.

Content ​

SELECT ​

A fully equipped SELECT query is represented as an object following this template (all properties except from are optional):

js
SELECT = {SELECT:{
  distinct: true,
  from: source | join,
  mixin: { ...element },
  columns: projection,
  excluding: [ ...string ],
  where: _xpr,
  groupBy: [ ...expr ],
  having: _xpr,
  orderBy: [ ...ordering_term ],
  limit: { rows:expr, offset:expr },
  forUpdate: { wait: number },
  forShareLock: { wait: number },
  search: _xpr,
  count: Boolean
}}
SELECT = {SELECT:{
  distinct: true,
  from: source | join,
  mixin: { ...element },
  columns: projection,
  excluding: [ ...string ],
  where: _xpr,
  groupBy: [ ...expr ],
  having: _xpr,
  orderBy: [ ...ordering_term ],
  limit: { rows:expr, offset:expr },
  forUpdate: { wait: number },
  forShareLock: { wait: number },
  search: _xpr,
  count: Boolean
}}
PropertyDescription
froma primary source or joined sources
mixina dictionary of several CSN element definitions
columnsan array of column expressions
excludingan array of names
wherea predicate expression
groupByan array of expressions
havinga predicate expression
orderByan array of ordering terms
limita dictionary of two expressions: rows and offset
searcha predicate expression
counta Boolean
js
source         =  ( ref | SELECT ) + { as:string }
join           =  { join:string, args:[...source], on:_xpr }
projection     =  [ ...column_expr ]
column_expr    =  expr + { as:string, cast:def, (expand|inline):projection }
ordering_term  =  expr + { sort: 'asc'|'desc', nulls: 'first'|'last' }
source         =  ( ref | SELECT ) + { as:string }
join           =  { join:string, args:[...source], on:_xpr }
projection     =  [ ...column_expr ]
column_expr    =  expr + { as:string, cast:def, (expand|inline):projection }
ordering_term  =  expr + { sort: 'asc'|'desc', nulls: 'first'|'last' }

Sources are references or subqueries with an optional:

  • as – a string specifying a chosen source alias

Joins combine two sources with these properties:

Column Expressions are a plain string '*', or expressions with these optional additional properties:

Ordering Terms are expressions, usually references, with one or none of...

  • sort = 'asc' | 'desc'
  • nulls = 'first' | 'last'

Example ​

For example, the following query in CQL:

sql
SELECT from samples.bookshop.Books {
  title, author.name as author,
  1 as one,
  x+2 as two : Integer,
} excluding {
  dummy
}
WHERE ID=111
GROUP BY x.y
HAVING x.y<9
ORDER BY title asc
LIMIT 11 OFFSET 22
SELECT from samples.bookshop.Books {
  title, author.name as author,
  1 as one,
  x+2 as two : Integer,
} excluding {
  dummy
}
WHERE ID=111
GROUP BY x.y
HAVING x.y<9
ORDER BY title asc
LIMIT 11 OFFSET 22

is represented in CQN as:

js
CQN = {SELECT:{
  from: {ref:["samples.bookshop.Books"]},
  columns: [
    {ref:["title"]},
    {ref:["author","name"], as: "author"},
    {val:1, as: "one"},
    {xpr:[{ref:['x']}, '+', {val:2}], as: "two",
      cast: {type:"cds.Integer"}
    }
  ],
  excluding: [
    "dummy"
  ],
  where: [{ref:["ID"]}, "=", {val: 111}],
  groupBy: [{ref:["x","y"]}],
  having: [{ref:["x","y"]}, "<", {val: 9}],
  orderBy: [{ref:["title"], sort:'asc' }],
  limit: {rows:{val:11}, offset:{val:22}}
}}
CQN = {SELECT:{
  from: {ref:["samples.bookshop.Books"]},
  columns: [
    {ref:["title"]},
    {ref:["author","name"], as: "author"},
    {val:1, as: "one"},
    {xpr:[{ref:['x']}, '+', {val:2}], as: "two",
      cast: {type:"cds.Integer"}
    }
  ],
  excluding: [
    "dummy"
  ],
  where: [{ref:["ID"]}, "=", {val: 111}],
  groupBy: [{ref:["x","y"]}],
  having: [{ref:["x","y"]}, "<", {val: 9}],
  orderBy: [{ref:["title"], sort:'asc' }],
  limit: {rows:{val:11}, offset:{val:22}}
}}

UPSERT ​

js
UPSERT = {UPSERT:{
   into: (ref + { as:string }) | string,
   entries: [ ...{ ...column:any } ],
   as: SELECT
}}
UPSERT = {UPSERT:{
   into: (ref + { as:string }) | string,
   entries: [ ...{ ...column:any } ],
   as: SELECT
}}

INSERT ​

js
INSERT = {INSERT:{
   into: (ref + { as:string }) | string,
   columns: [ ...string ],
   values: [ ...any ],
   rows: [ ...[ ...any ] ],
   entries: [ ...{ ...column:any } ],
   as: SELECT
}}
INSERT = {INSERT:{
   into: (ref + { as:string }) | string,
   columns: [ ...string ],
   values: [ ...any ],
   rows: [ ...[ ...any ] ],
   entries: [ ...{ ...column:any } ],
   as: SELECT
}}

Either and only one of the properties values or rows or entries is expected to be specified. Each of which is expected to have one or more entries:

  • values is an array of values, which positionally match to specified columns.
  • rows is an array of one or more values.
  • entries is an array of records with name-value pairs.

Examples:

js
CQN = {INSERT:{
  into: { ref: ['Books'] },
  columns: [ 'ID', 'title', 'author_id', 'stock' ],
  values: [ 201, 'Wuthering Heights', 101, 12 ]
}}
CQN = {INSERT:{
  into: { ref: ['Books'] },
  columns: [ 'ID', 'title', 'author_id', 'stock' ],
  values: [ 201, 'Wuthering Heights', 101, 12 ]
}}
js
CQN = {INSERT:{
  into: { ref: ['Books'] },
  columns: [ 'ID', 'title', 'author_id', 'stock' ],
  rows: [
    [ 201, 'Wuthering Heights', 101, 12 ],
    [ 251, 'The Raven', 150, 333 ],
    [ 252, 'Eleonora', 150, 234 ]
  ]
}}
CQN = {INSERT:{
  into: { ref: ['Books'] },
  columns: [ 'ID', 'title', 'author_id', 'stock' ],
  rows: [
    [ 201, 'Wuthering Heights', 101, 12 ],
    [ 251, 'The Raven', 150, 333 ],
    [ 252, 'Eleonora', 150, 234 ]
  ]
}}
js
CQN = {INSERT:{
  into: { ref: ['Books'], as: 'NewBooks' },
  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 }
  ]
}}
CQN = {INSERT:{
  into: { ref: ['Books'], as: 'NewBooks' },
  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 }
  ]
}}

The last one also allows to express so-called 'deep inserts'. Let's assume we want to store an author with two books:

js
CQN = {INSERT:{ into: { ref: ['Authors'] }, entries: [
  { ID:150, name:'Edgar Allen Poe', books:[
    { ID:251, title:'The Raven' },
    { ID:252, title:'Eleonora' }
  ] }
]}}
CQN = {INSERT:{ into: { ref: ['Authors'] }, entries: [
  { ID:150, name:'Edgar Allen Poe', books:[
    { ID:251, title:'The Raven' },
    { ID:252, title:'Eleonora' }
  ] }
]}}

Instead of inserting new entries for books we might want to just add relationships to already existing books, in that case just specify one or more primary key values of the target instance.

js
CQN = {INSERT:{ into: { ref: ['Authors'] }, entries: [
  { ID:150, name:'Edgar Allen Poe', books:[
    251, 252,
  ] }
]}}
CQN = {INSERT:{ into: { ref: ['Authors'] }, entries: [
  { ID:150, name:'Edgar Allen Poe', books:[
    251, 252,
  ] }
]}}

UPDATE ​

js
UPDATE = {UPDATE:{
   entity: ref + { as:string },
   data: { ...column:any },
   where: _xpr
}}
UPDATE = {UPDATE:{
   entity: ref + { as:string },
   data: { ...column:any },
   where: _xpr
}}

DELETE ​

js
DELETE = {DELETE:{
   from: ref + { as:string },
   where: _xpr
}}
DELETE = {DELETE:{
   from: ref + { as:string },
   where: _xpr
}}

CREATE ​

js
CREATE = {CREATE:{
   entity: entity | string,
   as: SELECT
}}
CREATE = {CREATE:{
   entity: entity | string,
   as: SELECT
}}

DROP ​

js
DROP = {DROP:{
   table: ref,
   view: ref,
   entity: ref
}}
DROP = {DROP:{
   table: ref,
   view: ref,
   entity: ref
}}

Examples:

js
CQN = {DROP:{
  table: { ref: ['Books'] }
}}
CQN = {DROP:{
  table: { ref: ['Books'] }
}}
js
CQN = {DROP:{
  view: { ref: ['Books'] }
}}
CQN = {DROP:{
  view: { ref: ['Books'] }
}}
js
CQN = {DROP:{
  entity: { ref: ['Books'] }
}}
CQN = {DROP:{
  entity: { ref: ['Books'] }
}}