Examples of data queries for the machine learning model

Level: advanced

note

The functionality is relevant to Classic UI.

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.

Important

An exception will be thrown if the type is not supported.

static Select Cols(this Select select, params object[] sourceColumns)

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

static Select Count(this Select select, object sourceColumn)

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

static Select Coalesce(this Select select, params object[] sourceColumns)

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

static Select DateDiff(this Select select, DateDiffQueryFunctionInterval interval, object startDateExpression, object endDateExpression)

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

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