Creating data queries for the machine learning model
Glossary Item Box
Introduction
Use the Terrasoft.Core.DB.Select class instance for queries of training data or data for predicting machine learning service (see “Machine learning service” and “How to implement custom prediction model”). It is dynamically imported by the Terrasoft.Configuration.ML.QueryInterpreter.
ATTENTION
The QueryInterpreter interpreter does not allow the use of lambda expressions.
Use the provided userConnection variable as an argument of the Terrasoft.Core.UserConnection type in the Select constructor when building query expression. The column with the “Id” alias (the unique id of the target object instance) is a required in the query expression.
The Select expression can be complex. Use the following practices to simplify it:
- Dynamic adding of types for the interpreter.
- Using local variables.
- Using the Terrasoft.Configuration.QueryExtensions utility class.
Dynamic adding of types for the interpreter
You can dynamically add types for the interpreter. For this the QueryInterpreter class provides the RegsiterConfigurationType and RegisterType methods. You can use them directly in the expression. For example, instead of direct using the type id:
new Select(userConnection) .Column("Id") .Column("Body") .From("Activity") .Where("TypeId").IsEqual(Column.Const("E2831DEC-CFC0-DF11-B00F-001D60E938C6"));
you can use the name of a constant from dynamically registered enumeration:
RegisterConfigurationType("ActivityConsts"); new Select(userConnection) .Column("Id") .Column("Body") .From("Activity") .Where("TypeId").IsEqual(Column.Const(ActivityConsts.EmailTypeUId));
Using local variables
You can use local variables to avoid code duplication and more convenient structuring. Constraint: the type of the variable must be statically calculated and defined by the var word.
For example, the query with repetitive use of delegates:
new Select(userConnection) .Column("Id") .Column("Body") .From("Activity") .Where("CreatedOn").IsGreater(Func.DateAddMonth(-1, Func.CurrentDateTime())) .And("StartDate").IsGreater(Func.DateAddMonth(-1, Func.CurrentDateTime()));
you can write in a following way:
var monthAgo = Func.DateAddMonth(-1, Func.CurrentDateTime()); new Select(userConnection) .Column("Id") .Column("Body") .From("Activity") .Where("StartDate").IsGreater(monthAgo) .And("ModifiedOn").IsGreater(monthAgo);
Using the Terrasoft.Configuration.QueryExtensions utility class
The Terrasoft.Configuration.QueryExtensions utility class provides several extending methods for the Terrasoft.Core.DB.Select. This enables to build more compact queries.
As the object sourceColumn argument you can use following types (they will be transformed to the Terrasoft.Core.DB.QueryColumnExpression) for all extending methods:
- System.String – the name of the column in the “TableAlias.ColumnName as ColumnAlias” format (where the TableAlias and ColumnAlias are optional) or “*” – all columns.
- Terrasoft.Core.DB.QueryColumnExpression – will be added without changes.
- Terrasoft.Core.DB.IQueryColumnExpressionConvertible – will be converted.
- Terrasoft.Core.DB.Select – will be considered as subquery.
ATTENTION
An exception will be thrown if the type is not supported.
Terrasoft.Configuration.QueryExtensions use cases
1. The public static Select Cols(this Select select, params object[] sourceColumns) method
Adds specified columns or subexpressions to the query.
Using the Cols() extension method, instead of the following expression:
new Select(userConnection) .Column("L", "Id") .Column("L", "QualifyStatusId") .Column("L", "LeadTypeId") .Column("L", "LeadSourceId") .Column("L", "LeadMediumId").As("LeadChannel") .Column("L", "BusinesPhone").As("KnownBusinessPhone") .From("Lead").As("L");
you can write:
new Select(userConnection).Cols( "L.Id", "L.QualifyStatusId", "L.LeadTypeId", "L.LeadSourceId", "L.LeadMediumId AS LeadChannel", "L.BusinesPhone AS KnownBusinessPhone") .From("Lead").As("L");
2. The public static Select Count(this Select select, object sourceColumn) method
Adds an aggregation column to calculate the number of non-empty values to the query.
For example, instead:
var activitiesCount = new Select(userConnection) .Column(Func.Count(Column.Asterisk())) .From("Activity")
you can write:
var activitiesCount = new Select(userConnection) .Count("*") // You can also specify the column name. .From("Activity")
3. The public static Select Coalesce(this Select select, params object[] sourceColumns) method
Adds a column with the function of determining the first value not equal to NULL to the query.
For example, instead:
new Select(userConnection) .Cols("L.Id") .Column(Func.Coalesce( Column.SourceColumn("L", "CountryId"), Column.SourceColumn("L", "CountryId"), Column.SourceColumn("L", "CountryId"))) .As("CountryId") .From("Lead").As("L") .LeftOuterJoin("Contact").As("C").On("L", "QualifiedContactId").IsEqual("C", "Id") .LeftOuterJoin("Account").As("A").On("L", "QualifiedAccountId").IsEqual("A", "Id");
you can write:
new Select(userConnection) .Cols("L.Id") .Coalesce("L.CountryId", "C.CountryId", "A.CountryId").As("CountryId") .From("Lead").As("L") .LeftOuterJoin("Contact").As("C").On("L", "QualifiedContactId").IsEqual("C", "Id") .LeftOuterJoin("Account").As("A").On("L", "QualifiedAccountId").IsEqual("A", "Id");
4. The public static Select DateDiff(this Select select, DateDiffQueryFunctionInterval interval, object startDateExpression, object endDateExpression) method
Adds a column that specifies the date difference to the query.
For example, instead:
new Select(_userConnection) .Cols("Id") .Column(Func.DateDiff(DateDiffQueryFunctionInterval.Day, Column.SourceColumn("L", "CreatedOn"), Func.CurrentDateTime())).As("LeadAge") .From("Lead").As("L");
you can write:
var day = DateDiffQueryFunctionInterval.Day; new Select(userConnection) .Cols("L.Id") .DateDiff(day, "L.CreatedOn", Func.CurrentDateTime()).As("LeadAge") .From("Lead").As("L");
5. public static Select IsNull(this Select select, object checkExpression, object replacementValue)
Adds a column with the function replacing NULL value with a replacement expression.
For example, instead:
new Select(userConnection).Cols("Id") .Column(Func.IsNull( Column.SourceColumn("L", "CreatedOn"), Column.SourceColumn("L", "ModifiedOn"))) .From("Lead").As("L");
you can write:
new Select(userConnection).Cols("L.Id") .IsNull("L.CreatedOn", "L.ModifiedOn") .From("Lead").As("L");