Adding columns to a query
Glossary Item Box
Terrasoft.EntitySchemaQuery query column is Terrassoft.EntityQueryColumn class instance. You can specify main characteristics of column instance in its properties: title, display value, checkboxes, sorting order and direction etc.
addColumn() method that returns instance of column, added to query, is designed for adding columns to queries. The column name relative to root schema is formed in addColumn() methods in accordance with rules, described in Building of paths to columns relative to root schema. This method has several variants that allow for adding columns with different parameters to a query (table 1).
Table 1. — Method of adding columns to query
Method | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
addColumn(column,[columnAlias])
Creates and adds Terrasoft.Entity.QueryColumn column instance to query column collection.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
addAggregationSchemaColumn(columnPath, aggregationType, [columnAlias], aggregationEvalType)
Creates and adds Terrasoft.FunctionQueryColumn functional column instance with set aggregation type (Terrasoft.FunctionType.AGGREGATION) to query column collection.
Aggregation types (Terrasoft.AggregationType)
Application field of aggregation function (Terrasoft.AggregationEvalType)
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
addParameterColumn(paramValue, paramDataType, [columnAlias])
It creates and adds Terrasoft.ParemeterQueryColumn parameter column instance to column correlation.
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
addFunctionColumn(columnPath, functionType, [columnAlias])
It creates and adds Terrasoft.FunctionQueryColumn function column instance to column collection.
Function type (Terrasoft.FunctionType)
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
addDatePartFunctionColumn(columnPath, datePartType, [columnAlias])
It creates and adds Terrasoft.FunctionQueryColumn function column instance with [Date Part] type (Terrasoft.FucntionType.DATE_PART) to query column collection.
Data part(Terrasoft.DatePartType)
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
addMacrosColumn(macrosType, [columnAlias])
It creates and adds Terrasoft.FunctionQueryColumn function column instance with [Macros] type (Terrasoft.FunctionType.MACROS) that doesn't require parameterization (for example, current month, current user, primary column etc.) to column collection.
Macros column types (Terrasoft.QueryMacrosType)
|
||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
addDatePeriodMacrosColumn(macrosType, [macrosValue], [columnAlias])
It creates and adds Terrasoft.FunctionQueryColumn function column instance with [Macros] type (Terrasoft.FucntionType.MACROS) to query column columns. The function adds column with macros type that requires parameterization. For example, next N days, the 3d quarter of the year etc.
|
Examples of addition of columns to query
Example 1. — Adding query column from root schema to query column collection
var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, { rootSchemaName: "Activity" }); esq.addColumn("DurationInMinutes", "ActivityDuration");
Example 2. — Adding aggregation column query with SUM aggregation type, applied to all table records, to query column collection
var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, { rootSchemaName: "Activity" }); esq.addAggregationSchemaColumn("DurationInMinutes", Terrasoft.AggregationType.SUM, "ActivitiesDuration", Terrasoft.AggregationEvalType.ALL);
Example 3. — Adding aggregation column query with COUNT aggregation type, applied to table unique records, to query column collection
var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, { rootSchemaName: "Activity" }); esq.addAggregationSchemaColumn("DurationInMinutes", Terrasoft.AggregationType.COUNT, "UniqueActivitiesCount", Terrasoft.AggregationEvalType.DISTINCT);
Example 4. — Adding parameter column with TEXT data type to query column type
var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, { rootSchemaName: "Activity" }); esq.addParameterColumn("DurationInMinutes", Terrasoft.DataValueType.TEXT, "DurationColumnName");
Example 5. — Adding function column with LENGTH function type (value size in bytes) to query column collection
var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, { rootSchemaName: "Activity" }); esq.addFunctionColumn("Photo.Data", Terrasoft.FunctionType.LENGTH, "PhotoLength");
Example 6. — Adding function column with Date-Part function type (date part) to query column collection. Week day is used as a value
var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, { rootSchemaName: "Activity" }); esq.addDatePartFunctionColumn("StartDate", Terrasoft.DatePartType.WEEK_DAY, "StartDay");
Example 7. — Adding function column with MACROS type that don't require parameterization, i.e. PRIMARY_DISPLY-COLUMN (Primary Display Column), to query column collection
var esq = this.Ext.create(Terrasoft.EntitySchemaQuery, { rootSchemaName: "Activity" }); esq.addMacrosColumn(Terrasoft.QueryMacrosType.PRIMARY_DISPLAY_COLUMN, "PrimaryDisplayColumnValue");