Building of paths to columns relative toroot schema. Examples

The starting point of the EntitySchemaQuery building mechanism is a root schema and feedback principle (for more details see article The use of EntitySchemaQuery for creation of queries in database). 

In order to add a column from a table to a query you must build the path to this column. There are different variants for adding columns to queries. Examples of the name formation of columns in each variant are shown below.

1) Root schema column

In this case, the column name is built as [Column name in root schema].

• Root schema: Contact
• Example: column with contact address
• Column name: Address
• Example of creation of the EntitySchemaQuery query that returns values of this column:

Example 1

// Let's create [EntitySchemaQuery] class instance with [Contact] root schema.
var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "Contact"
});
// Add [Address] column then add [Address] alias to it.
esq.addColumn("Address", "Address");

2) Schema column, lookup column of current schema refers to

The column name is built on the principle [Lookup column name].[Schema column name, lookup refers to].

Country schema are jointed to City root schema by the JOIN operator (LEFT OUTER JOIN by default) in a resultant query. A joint condition (On condition of JOIN operator) is formed on the following principle:
[Name of joinable schema].[Id] = [Root schema name].[Name of column that refers to joinable schema + Id]

In common cases you can continue to build a feedback chain.

• Root schema: Contact
• Example: column with account name, column with name of main contact of account
• Column names: Account.Name, Account.PrimaryContact.Name
• Example of creation of EntitySchemaQuery query that returns values of these columns:

Example 2

//Let's create [EntitySchemaQuery] class instance with [Contact] root schema.
var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "Contact"
});
// Add [Account] lookup column. 
// Then add [Name] column from [Account] schema, 
// to which [Account] lookup column refers, and assign [AccountName] alias to it .
esq.addColumn("Account.Name", "AccountName");
// Add [Account] lookup column. 
// Then add [PrimaryContact] lookup column from [Account] schema, 
// to which [Account] lookup column refers. 
// Add [Name] column from [Contact] schema,
// to which [PrimaryContact] lookup column refers and assign [PrimaryContactName] alias to it.
esq.addColumn("Account.PrimaryContact.Name", "PrimaryContactName");

3) Schema column on random external key

Column name is built on the following principle [Name of _ joinable_schema: Name of _column_for_linking of_joinable_schema:Name of_column_for_ linking of _current_schema].

If ID column is used as column for linking in current schema, it can be omitted, i.e. column name will have the following view:
[Name of_ joinable _ schema:Name of _ column_ for _linking of _joinable _schema].

In general, you can build the column names by the chains of reverse connections of any length.

• Example: column with name of the contact that has added city
• Column name: [Contact:Id:CreatedBy].Name
• Example of creation of EntitySchemaQuery of returning value of this column:

Example 3

// Let's create [EntitySchemaQuery] class instance with root schema [Contact].
var esq = this.Ext.create("Terrasoft.EntitySchemaQuery", {
    rootSchemaName: "Contact"
});
// Add one more [Contact] schema to [Owner] column 
// and select [Name] column from it. Assign [OwnerName] alias to it.
esq.addColumn("[Contact:Id:Owner].Name", "OwnerName");
// Let's joint [Contact] schema to [Acount] lookup column on [PrimaryContact] column 
// and select [Name] column from it. 
// Assign [PrimaryContact] alias to it.
esq.addColumn("Account.[Contact:Id:PrimaryContact].Name", "PrimaryContactName");

See Also