ECSQL in iTwin.js Code Examples
This page contains generic example code that can be used across the IModelDb, ECDb, and IModelConnection classes. In the examples, the identifier iModel
is used as an object that could be any of those classes.
For more info and examples specific to running in the frontend and backend, check out:
See also:
The createQueryReader
Function
All of the iModel classes above provide a createQueryReader
method for executing ECSQL statements on an iModel and reading the results of the query. The execution and results are handled by the returned ECSqlReader.
For refererence, here are all three
createQueryReader
methods.
Here is the TypeScript method signature for createQueryReader
:
The
ecsql
string is the ECSQL statement that will be executed on the iModel. This is where you provide an ECSQL statement to query an iModel. E.g.,The
params
argument of type QueryBinder contains any bindings for the ECSQL statement.The
config
argument of type QueryOptions is for additional options for how the query will be executed. Some examples are:rowFormat
for determining how query results will look. For an explanation of the available formats, see ECSQL Row Formats.limit
for specifying how many rows can be returned at most.restartToken
for canceling the execution of a previous query and starting a new one.
Iterating Over Query Results
Use the ECSqlReader created by the createQueryReader
function to iterate over query results. There are three primary ways to do so:
1. Stream them using ECSqlReader as an asynchronous iterator.
Results are QueryRowProxy objects. See Handling a Row of Query Results for how to handle the results.
2. Iterate over them manually using ECSqlReader.step.
Results are QueryRowProxy objects. See Handling a Row of Query Results for how to handle the results.
3. Capture all of the results at once in an array using QueryRowProxy.toArray.
Results are JavaScript literals. See Working with Rows as JavaScript Literals for how to handle the results.
Handling a Row of Query Results
The format of the query results is dependent on the provided rowFormat
in the config
parameter. Click here to read about ECSQL Row Formats in detail.
When iterating over each row one at a time (as an asynchronous iterator or with step
), each row will be a QueryRowProxy object. The rows value can then be accessed by column index or by name.
Accessing Row Values By Index
When iterating with a for loop:
When iterating with step
:
The
rowFormat
used does not matter when accessing by index; only the order of the selected columns does. The two queries below will return the ECInstanceId and ECClassId values as indexes 0,1 and 1,0 respectively.
Accessing Row Values By Name
When iterating with a for loop:
When iterating with step
:
Using Types with the Row Results
Each ECSQL value has a corresponding TypeScript type which is described in ECSQL Parameter Types.
Working with Rows as JavaScript Literals
Call .toRow()
on the row to convert it from a QueryRowProxy
object to a JavaScript literal. The format of the literal is dependent on the provided rowFormat
in the config
parameter. Check out ECSQL Row Formats for more details.
Note: With the deprecation of
.query
in 3.7 and the switch to using ECSqlReader to handle query results, rows were changed from being JavaScript literals toQueryRowProxy
s. Using.toRow()
may fix any issues that emerged due to this change.
When iterating with a for loop:
When iterating with step
:
When using toArray
:
Specifying Row Formats
The format of of a row is dependent on the provided rowFormat
in the config
parameter of createQueryReader
. The row formats are specified by supplying a QueryRowProxy enum.
Check out ECSQL Row Formats for more details.
QueryRowFormat.UseECSqlPropertyIndexes
This is the default format when no rowFormat
is specified. Column values should refered to by an index which is ordered by the columns specified in the SELECT statement.
Here is an example using .toArray
:
Example Output:
Notice that the individual rows are returned as arrays.
QueryRowFormat.UseECSqlPropertyNames
Column values should refered to by their ECSQL property names.
Here is an example using .toArray
:
Example Output:
QueryRowFormat.UseJsPropertyNames
Column values should be refered to by their JavaScript property names. The mapping from ECSQL property names to JavaScript property names is described in ECSQL Row Formats.
Here is an example using .toArray
:
Example Output:
Notice how the keys in the above JSON are converted from ECProperty names to names that conform to JavaScript standards as described in ECSQL Row Formats. For example, "ECInstanceId" is mapped to "id".
Parameter Bindings
See ECSQL Parameter Types to learn which types to use for the parameters when binding.
Positional parameters
Named parameters
Navigation properties
Navigation properties are structs made up of the Id of the related instance and the backing ECRelationshipClass. The NavigationBindingValue interface is used to bind values to navigation property parameters.
Because of the struct nature of navigation properties, you can also use its members in the ECSQL. The following example illustrates this by specifying the Id member of a navigation property.
Struct properties
You can either parameterize a struct property as a whole or parameterize individual members of the struct. See Struct properties in ECSQL for the ECSQL background.
The ECSQL examples used in this section refer to the sample ECSchema in Struct properties in ECSQL.
Binding structs as a whole
Binding to individual struct members
The two ECSQL examples used in this section amount to the same results.
Array properties
See Array properties in ECSQL for the ECSQL background.
The ECSQL examples used in this section refer to the sample ECSchema in Array properties in ECSQL.
Last Updated: 17 December, 2024