Querying

Extended supports querying for extracting lists of objects. The resulting lists are commonly used for generating tables, or for iterating over each element.

Keywords

Keyword	Definition
SELECT	Selects objects. Signifies start of query.
FROM	Optional. Specifies the root where query will search from.
WHERE	Optional. Accepts conditions that should be met by objects being queried.

SELECT

Signifies the start of a query. All queries must begin with this keyword.

Syntax	Description
SELECT <object type>	Selects objects of the specified type
SELECT <object type1>, [<object type2>], [<object type3>], ...	Selects objects of the specified types

Examples

SELECT Kpi                      // Returns a list of all the KPIs in the model

SELECT Kpi, Scorecard           // Returns a list of all the KPIs and Scorecards in the model

FROM

Optional keyword. The clause following it specifies the root of where the query will search from. All children under the specified root will also be included in the search. If not specified, query will default to searching from entire model. FROM allows you to query from additional spaces besides the model, such as the Template.

Syntax	Description
SELECT <object type> FROM <root>	Selects objects from specified root
SELECT <object type> FROM <root1>, [<root2], [<root3>],...	Selects objects from specified roots

Examples

SELECT Perspective FROM this.organisation       // Returns a list of all the Perspectives under the
                                                   context Organisation or its children

SELECT Kpi FROM this.object                     // Returns a list of all the Kpis under the
                                                   context object

SELECT Kpi FROM t.LIBRARY_KPI                   // Returns a list of all the Kpis under the object
                                                   with id "LIBRARY_KPI"

SELECT FunctionStatus from t.150318             // Returns a list of all the FunctionStatuses
                                                   under the object with id "150318"

WHERE

Optional keyword. The clauses following it specifies one more more conditional statements that must be met by the objects being queried. Multiple conditional statements can be written by using the operators AND and OR. Boolean properties can be used as standalone conditional statements since they resolve to a Boolean. Relational Operators, including IN and CONTAINS can be used in the conditional statements.

Syntax	Description
SELECT <object type> WHERE <condition>	Select objects that meet the condition
SELECT <object type> FROM <root> WHERE <condition	Select objects from specified root that meet the condition
SELECT <object type> WHERE <condition1> AND <condition2>	Select objects that the meet both the conditions
SELECT <object type> WHERE <condition1> OR <condition2>	Select objects that meet either of the conditions

Examples

SELECT Organisation WHERE orgType = "Franchise"     // Returns a list of all the organisations with an
                                                    // orgType property of Franchise

SELECT ManualRiskFactor                             // Returns a list of Risk Factors whose riskOwner is in the group with ID "VPGROUP"
    WHERE riskOwner IN g.VPGROUP

SELECT actionPlan WHERE active                      // Returns a list of all the Strategic Initiatives that are
                                                    // active. "active" is a Boolean property

SELECT Kpi FROM this.organisation                   // Returns a list of all the Kpis under the context
    WHERE responsible CONTAINS this.user            // organisation that have a red status and the context user
    AND statusClassification = RED                  // in their responsible property

SELECT KPI FROM this.object                         // Returns a list of all the Kpis under the context object
    WHERE parent.parent.name = "Finance"            // that are located within the "Finance" or "Customer"
    OR parent.parent.name = "Customer"              // Perspectives, and that have an actual value greater than 0
    AND actual > 0

Model Roots

The following are the roots for each individual model. These can be used with the FROM keyword.

Token	Model
root.accessProfile	Access Profiles
root.actionPlan	Strategic Initiatives
root.classConfig	Class Configuration (where properties are linked classes)
root.custom_Period	Custom Period
root.defaults	Default Objects
root.expression	Expressions
root.externalResource	Resources
root.forms	Forms
root.group	Groups
root.node	Nodes
root.nodeDataImport	Node Data Imports
root.nodeType	Node Types
root.notification	Workflow
root.organisation	Organisations
root.page	Pages
root.portal	Shared Web Items
root.property	Property Settings
root.reporter	Reporter
root.risk	Risks
root.templateCategory	Template
root.transformer	Transformer
root.user	User

Query Performance

The time it takes to execute queries can vary greatly based on the specified root, WHERE conditions, and the order of the conditions. Conditions are evaluated in the order that they appear in the query. Some best practices are:

Use the smallest root possible

All of the children under a root will be searched. So if you only want Kpis from a specific scorecard, use that Scorecard as the root. Remember that queries with no FROM clause will search everywhere.

Write faster conditions first

Conditions don’t evaluate at the same speed. For example, string comparison conditions (name = "Scorecard") evaluate much faster than conditions comparing statuses (statusClassification = GREEN). For this reason, it is better to write the faster-evaluating conditions first.

Write more demanding conditions first

Because conditions are evaluated in the order they appear, writing the most demanding conditions first can improve the performance of the query. This is because the subsequent conditions would only have to be applied to a smaller subset of objects, instead of the whole root.

Write properties of the queried type as the first operand

When writing queries with conditions that compare an object property to a value, write the property as the first operand. Behind the scenes, this will allow the query to take advantage of an indexer, resulting in performance that is an order of magnitude faster.

// BAD (value is first operand)
SELECT Kpi WHERE 5 = numberProperty

// GOOD (object property is first operand)
SELECT Kpi WHERE numberProperty = 5

SELECT will always be faster, or at least equally fast as .filter()

SELECT should be used for the “heavy lifting” and .filter() should be reserved for making small reductions to an existing list. A User that finds themselves using repetitive/duplicate .filter() conditions after their initial SELECT should consider placing those conditions in their initial SELECT where possible.