DbQuery

DbQuery TypeScript Class Reference

Overview

The DbQuery class provides methods to execute data queries against the local (on-device) database. This is the preferred approach to work with local relational data. It is used to DbQuery to select, insert, update and delete business objects.

Properties
Methods
Constructor
App Types
Properties

There are no properties

Methods
Constructor

Don't instantiate directly instances of the DbQuery class. DbQuery instances are created through a call to the query() method of the BusinessObjectBase assessor. See also BusinessObjectBase.query() method

import {DbQuery} from '@dms';
import { Customer } from '@dms-bo';
...
const query = Customer.query();
App Types

📳 Mobile 🌐 Backend

fields(fields: Array<string>): DbQuery<T>

Description
Parameters
Returns
Example
Description

fields method sets the list of the fields to be returned by the executeSelect method of DbQuery. It provides functionality similar to the SELECT clause in a sql statement.

Parameters
  • fields: Array<string> array containing the name of the business object members to be returned by executeSelect method.

Returns

DbQuery instance, allowing methods chaining.

Example

import {DbQuery} from '@dms'
import {Customer} from '@dms-bo'
const query = Customer.query();
//define only 3 fields to be returned
query.fields(['Id','Name','City']);
//method chaining - e.g. calling several method on a single line
query.fields(['Id','Name','City']).filter("Id='123'");

filter(filterExpression: string): DbQuery<T>

Description
Parameters
Returns
Example
Description

Allows the developer to define a filter to be applied, when using executeSelect() method. The filter expects the same syntax as WHERE clause in sql statement.

Parameters
  • filterExpression: string the filter to be applied during a call to executeSelect method

Returns

DbQuery instance, allowing method chaining.

Example

import {DbQuery} from '@dms'
import {Customer} from '@dms-bo'
const query = Customer.query();
//equal
query.filter("Id=1123'");
//greater than
query.filter("Revenue > 6000");
//more complex filter with parentess
query.filter("((Revenue > 6000 or Revenue=0) and (City='Sofia'))");
const result = await query.executeSelect();
//method chaining - e.g. calling several method on a single line
const result2 = await query.fields(['Id','Name','City','Revenue']).filter("City='Dubai'and Revenue>1234").executeSelect();

limit(maxRecords: number): DbQuery<T>

Description
Parameters
Returns
Example
Description

Allows the developer to limit the number of the records returned by the query.

Parameters
  • max - maximum number of records to be returned by the executeSelect() method

Returns

DbQuery instance, allowing method chaining.

Example

import {DbQuery} from '@dms'
import {Customer} from '@dms-bo'
const query = Customer.query();
//limit to 100 records
query.max(100);
const result = await query.executeSelect();

page(index: number): DbQuery<T>

Description
Parameters
Returns
Example
Description

Allows the developer to define the page number to be returned in page indexing scenarios. This make sense only if the limit() method was used.

Parameters
  • index - the page index number to be returned by the executeSelect() method

Returns

DbQuery instance, allowing method chaining.

Example

import {DbQuery} from '@dms'
import {Customer} from '@dms-bo'
const query = Customer.query();
//limit to 100 records
query.max(100);
//select second page , e.g. records from 100th to 199th
query.page(2);
const result = await query.executeSelect();

group(groupExpression: string): DbQuery<T>

Description
Parameters
Returns
Example
Description

Allows the developer to define a query group expression to be used. This is the same as in the GROUP BY clause in Sql

Parameters
  • groupExpression - the actual group expression to be applied during the execution of the executeSelect() method

Returns

DbQuery instance, allowing method chaining.

Example

import {DbQuery} from '@dms'
import {Customer} from '@dms-bo'
const query = Customer.query();
query.group('City, Country');
const result = await query.executeSelect();
//method chaining - e.g. calling several method on a single line
const result2 = await query.group('City, Country').executeSelect();

aggregate(aggregateExpression: string): DbQuery<T>

Description
Parameters
Returns
Example
Description

Allows the developer to define a aggregate expression to be used. This is the same as in aggregates(max, count, min , avg, ...) in Sql Note that the aggregate() method can NOT be used together with fields() method - e.g. the developer must either fields() or aggregate() methods

Parameters
  • aggregateExpression - the actual aggregate expression to be applied during the execution of the executeSelect() method

Returns

DbQuery instance, allowing method chaining.

Example

import {DbQuery} from '@dms'
import {Customer} from '@dms-bo'
const query = Customer.query();
query.aggregate('max(Revenue) as revenue, count(*) as cnt');
const result = await query.executeSelect();
//method chaining - e.g. calling several method on a single line
const result2 = await query.group('City, Country').aggregate('max(Revenue) as revenue, count(*) as cnt').executeSelect();

sort(sortExpression: string): DbQuery<T>

Description
Parameters
Returns
Example
Description

Allows the developer to define a sort expression to be used. This is the same as in ORDER BY clause in Sql

Parameters
  • sortExpression - the actual sorting expression to be applied during the execution of the executeSelect() method

Returns

DbQuery instance, allowing method chaining.

Example

import {DbQuery} from '@dms'
import {Customer} from '@dms-bo'
const query = Customer.query();
query.sort('Revenue asc');
const result = await query.executeSelect();
//method chaining - e.g. calling several method on a single line
const result2 = await query.fields(['City','Country']).sort('Revenue asc').executeSelect();

executeSelect(): Promise<BusinessObjectBase:T>

Description
Parameters
Returns
Example
Description

executeSelect method performs the actual query execution and returns array of objects, which extends class BusinessObjectBase.

Parameters

Returns

the array of objects, where each object extends class BusinessObjectBase.

Example

import {DbQuery} from '@dms'
import {Customer} from '@dms-bo'
const query = Customer.query();
//fetch all records from the local on-device database
const result = await query.executeSelect();
//iterating over the result set and call the update method for each businessobjectbase instance
//which will execute update operation against the local on-device database
result.forEach(function(record){
const customer = (record as Customer);
console.log(`Name:${customer.name},Revenue:${customer.Revenue}`);
customer.Revenue += 1000;
await customer.update();
});