DbQuery is a class representing a query to a database which structure is described by DbModel.

It uses some basic SQL concepts, but corresponds to its very limited subset.

The purpose of DbQuery objects is to facilitate the SQL generation in simple cases like backing Web search forms with multiple mostly independent filters on scalar fields.

Properties

Name	Type	Description
model	DbModel	The model this query belongs to
lang	DbLang	Copy of model.lang
columns	Map String => DbQueryColumn	all data columns processed by this query (the SELECT clause)
tables	[DbQueryTable]	the sequence of tables or similar objects for the FROM clause
order	[DbQueryColumn]	the sequence of columns constituting the ORDER BY clause
options	Object	copy of the options argument to constructor
root	DbQueryTable	tables[0] (computed property)

Constructor

Not to be invoked directly. Call DbModel#createQuery instead:

const myQuery = myModel.createQuery (
  [
    ['users', {
      filters: [
        ['login', '=', login],
        ['hash',  'IN', {sql: 'SELECT compute_hash (?)', params: [password]}],
        ['id_role', 'IN', 
          myModel.createQuery ([
            ['roles', {
              columns: ['id'], 
              filters: ['label', '=', 'admin'](/do-/node-doix-db/wiki/'label',-'=',-'admin')
            }]
          ])
        ],
      ],
    }], 
    ['roles', {columns: ['label']}]
  ], 
  {
    order: [
      'label', 
      ['roles.label', true]
    ]
  }
)

Parameters

Name	Type	Description
model	DbModel	The model this query belongs to
from	[String, Object](/do-/node-doix-db/wiki/String,-Object)	List of arguments for addTable calls
options	Options	Bag of options

Parameters

Name	Type	Description
order	[Array]	Array of argument lists for orderBy. [name, false] may be replaced by scalar name string.
limit	int > 0	NOT SUPPORTED BY default DbLang. Reserved for generating 'LIMIT', 'TOP' and similar SQL clauses.
offset	int >= 0	NOT SUPPORTED BY default DbLang. Reserved for generating 'OFFSET' and similar SQL clauses along with limit.

Methods

addTable

Creates a new DbQueryTable and adds it to the tables list.

myQuery.addTable ('users', {alias: 'userz', columns: ['id_role', 'label']})
myQuery.addTable ('generate_series (1::int, 10)', {alias: 'ten')
myQuery.addTable ('roles') // alias by name, all columns from the model

orderBy

Adds an existing DbQueryColumn to the order list setting the desc property.

myQuery.orderBy ('id_role', true) // DESC
myQuery.orderBy ('label')

toParamsSql ()

Returns an Array of parameters with the SQL text appended to the end of it.

const params = myQuery.toParamsSql (), sql = params.pop ()
const records = myDb.getArray (sql, params)

toCountingQuery

Returns a copy of this query where:

• the only column is {sql: 'COUNT(*)', alias: 'cnt'};
• all tables with LEFT join are removed;
• the order list is empty.