Scroll

Advanced Joins

The 2017.2 release of Exago BI features several Join Enhancements, including the ability to join on inequalities, nested join expressions, and subqueries. Additionally, data objects can now be Cloned, which allows for multiple different joins between objects, as well as the ability to join objects to themselves.

These enhancements allow developers to programmatically define complex joins "on-the-fly" in the application API, without needing to write SQL directly.

Note. To modify advanced joins in the Exago UI, see Joins - Advanced Joins. Advanced joins are not supported in the Admin Console. Advanced joins cannot be applied across multiple data sources.

Join Expressions

The left and right side of application joins now support complex expressions with the following logic:

Operators: EQ (=), NE (<>), LT (<), GT (>), LE (<=), GE (>=), IN

Conjunctions: AND, OR

Expression types: Column, Constant, SubQuery, Expression

Example

The JoinColumn and KeyColumn API objects have been extended with support for the additional logic. The following example demonstrates how to write a complex join statement in the API:

// INNER JOIN Products ON Categories.CategoryId > Products.CategoryId
// OR (Categories.CategoryId = Products.CategoryId
// AND Categories.CategoryId = Products.ProductId)

var join = new Join(api.PageInfo)
{
  EntityFromName = "Categories",
  EntityToName = "Products",
  Type = (int)JoinType.Inner,
};

join.JoinColumns.Add(new JoinColumn(
  new KeyColumn(join.EntityFromName, "CategoryId", JoinExpressionType.Column),
  JoinComparison.GT,
  new KeyColumn(join.EntityToName, "ProductId", JoinExpressionType.Column),
  JoinConjunction.OR,
  0 // nesting level - number of parens surrounding the expression
  ));

join.JoinColumns.Add(new JoinColumn(
  new KeyColumn(join.EntityFromName, "CategoryId", JoinExpressionType.Column),
  JoinComparison.EQ,
  new KeyColumn(join.EntityToName, "CategoryId", JoinExpressionType.Column),
  JoinConjunction.AND,
  1
  ));

join.JoinColumns.Add(new JoinColumn(
  new KeyColumn(join.EntityFromName, "CategoryId", JoinExpressionType.Column),
  JoinComparison.EQ,
  new KeyColumn(join.EntityToName, "ProductId", JoinExpressionType.Column),
  JoinConjunction.OR,
  1
  ));

// api.Joins.Add(join);    // add at the application level
// report.Joins.Add(join); // or at the report level

Join XML Schema

The join XML schema has changed to support these enhancements. The <joincol> attribute is deprecated and replaced with <clause>:

<clause>
  <left_entity /> <!-- added in v2017.3 -->
  <left_side />
  <right_side />
  <conjunction />
  <comparison />
  <left_side_type />
  <right_side_type />
  <level />
</clause>

For details, see Config File XML Reference - Joins.

Reports created in older versions will be automatically converted to the new schema when they are saved in the Advanced Report Designer. The Exago BI configuration file will not be converted automatically.

Entity Cloning

Data Objects (entities) can be cloned on a per-report basis, to allow objects to be joined in multiple ways.

The following example demonstrates how to add a cloned entity to a report:

Example

report.Entities.Add(api.Entities.GetEntityClone(
  "Categories",    // original entity
  "Category_Clone" // cloned entity name
));

The clonedFrom property indicates the original entity that a clone was created from.

Entity Report XML Schema

The entity report XML schema was updated to support an optional <clone> attribute, indicating that an entity is a clone of an existing one:

<entity>
  <entity_name>Categories_Clone</entity_name>
  <clone>Categories</clone>
</entity>

Cloned entities can be added to reports by editing the XML directly, if desired.

Configuration Settings

Two hidden flags were added to the configuration file in support of entity cloning.

  • <aliasallentities>
  • <safemode>

Because entity names may no longer be unique, the entity Id attribute will now always be used as an alias in the generated SQL. This can be overridden by changing the <aliasallentities> attribute to False (default: True).

If <safemode> is set to True (default: False), during runtime the application will check if entity names and Ids are unique. If not, an Error is logged in the log file.


Hidden Article Information

Article Author
Exago Development
created 2017-10-19 16:50:49 UTC
updated 2018-04-24 13:53:18 UTC

Labels
conditional, object, clone, cloning, entity, inequality, expression, subquery, subselect,
Have more questions? Submit a request