Creatio development guide
PDF
This documentation is valid for Creatio version 7.15.0. We recommend using the newest version of Creatio documentation.

Using joined tables

Glossary Item Box

JOIN types of joined tables

The JOIN operator is used when adding a column from a schema (other than the root schema) to the query. The LEFT OUTER joining type is used by default. While adding columns from a non–root schema, EntitySchemaQuery enables you to specify the query joining type for this schema. To do this, simply specify the name of the column in the following format:

[Special character connection type] [Column name]

Table 1 – JOIN types of joined schemas.

Joining type. Special character for joining type Column name example
INNER JOIN "=" "=Name"
LEFT OUTER JOIN ">" ">Name"
RIGHT OUTER JOIN "<" "<Name"
FULL OUTER JOIN "<>" "<>Name"
CROSS JOIN "*" "*Name"

Example

Adding columns to a query using various schema joining types.

// Creating the query instance with the City root schema.
var esqResult = new EntitySchemaQuery(UserConnection.EntitySchemaManager, "City");

// A Country schema with the LEFT OUTER JOIN connection type will be added to the query.
esqResult.AddColumn("Country.Name");

// A Country schema with the INNER JOIN connection type will be added to the query.
esqResult.AddColumn("=Country.Name");

// Two schemas will be connected to the query:
// 1) The Country schema with the LEFT OUTER JOIN connection type;
// 2) The Contact schema with the RIGHT OUTER JOIN connection type.
esqResult.AddColumn(">Country.<CreatedBy.Name");

// Text of the resulting sql-query (MS SQL):

// SELECT
//            [Country].[Name] [Country.Name],
//            [Country1].[Name] [Country1.Name],
//            [CreatedBy].[Name] [CreatedBy.Name]
// FROM
//            [dbo].[City] [City]
//            LEFT OUTER JOIN [dbo].[Country] [Country] ON ([Country].[Id] = [City].[CountryId])
//            INNER JOIN [dbo].[Country] [Country1] ON ([Country1].[Id] = [City].[CountryId])
//            LEFT OUTER JOIN [dbo].[Country] [Country2] ON ([Country2].[Id] = [City].[CountryId])
//            RIGHT OUTER JOIN [dbo].[Contact] [CreatedBy] ON ([CreatedBy].[Id] = [Country2].[CreatedById])  

Overlapping access rights to joined schemas

If the root schema of the query is administered by records and the query contains joinable schemas, then the access rights of the current user can be applied to them. Table 2 shows all the possible options for overlapping access rights to the joined schemas. These values correspond to the members of the Terrasoft.Core.DB.QueryJoinRightLevel enumeration.

Table 2 – Options for overlapping access rights to joinable query schemas

Enumeration member value Order of access rights overlap
EnabledAlways = 0 Always enable access rights overlap.
EnabledForAdditionalColumns = 1 Apply only if the query uses columns of the related schema that are other than the primary column and primary display column.
Disabled = 2 Do not overlap access rights.

The order of access rights overlap is determined by the JoinRightState property of a query. The default value of this property is determined by the QueryJoinRightLevel system setting, which pulls its values according to table 2. If the value of this system setting is not specified, then its default value is assumed to be [EnabledForAdditionalColumns].

© Creatio 2002-2020.

Did you find this information useful?

How can we improve it?