Creating data queries for the machine learning model

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.

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.

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");