Queries to columns referring to more classes

ObjectGears provides a column enabling to refer to data from more than one class. If we work with such a column in a query, we have to use special syntax that we will demonstrate on an example of a class Task referring in one column to class Account, Lead, Opportunity, Case, Contract, Campaign, Event and other (see model Business).

In the below example we want to create a query displaying Tasks, that refer to Account. We are going to create the query with a purpose to link it into the Master-Detail relation of Account. Therefore, the query has to have a column referring to Account. First we will create a basic frame of the query and in the second step we will enrich the query with other useful columns.

In the first query we are selecting columns Task Id, its Short description and mainly creating  column referring to class Account, through which we will be able to link the query into the master-detail relation.

Data of column referring to more classes are stored in more tables. We are using the one concerning Account and the name of which contains Id of the class Task and Id of the class Account (DatarowX_YLink, where X is Id of the class Task and Y Id of the class Account). Relations of tables are displayed in the below scheme.

Table DatarowX_YLink contains data from all the columns of class Task (X), that are referring to more classes and refer also to class Account (Y). Therefore, we have to define double relation of the class Account and table DatarowX_YLink - to the value stored in the referring column and to the column itself.

Both relations are stored in columns of the table DatarowX_YLink. The first relation represents Id of the particular Task record, that is in column ReferentionId. The second relation determines column Id of the class Task that participates in the reference - Id is stored in column ColumnClassDefId.

Due to portability we will not state in the name of the table DatarowX_YLink X and Y directly in the query definition but we will find them out by means of notation {{: class.classcode.id :}}. In the same way we will find out Id of the column related_to by means of notation {{: class.classname.column.columncode.id :}} when comparing values in column ColumnClassDefId.

SELECT t.id, t.shortdescription, l.LinkReferentionId link___account
FROM {{: class.task :}} t
INNER JOIN DataRow{{: class.task.id :}}_{{: class.account.id :}}Link l ON l.ReferentionId = t.id and l.ColumnClassDefId = {{: class.task.column.related_to.id :}}

 We will get a query with following basic columns.

Once this frame is defined, we can easily extend the query by other columns of class Task and by FullId of particular Task and Account. We will use these FullId consisting of class Id and record Id as URL in query columns, in order we can easily navigate from the master-detail relation of Account to Task including return URL back to Account record. 

SELECT t.id, t.shortdescription, t.code, t.name, t.description, t.target_date, l.LinkReferentionId link___account, s.id AS status___status,
p.fullname AS solver, sg.id AS status_group___status_group,
CONVERT( varchar,{{: class.account.id :}}) + '-' + CONVERT( varchar, l.LinkReferentionId) AS AccountFullId,
CONVERT( varchar,{{: class.task.id :}}) + '-'+ CONVERT( varchar, t.id) AS TaskFullId
FROM {{: class.task :}} t
INNER JOIN DataRow{{: class.task.id :}}_{{: class.account.id :}}Link l on l.ReferentionId = t.id and l.ColumnClassDefId = {{: class.task.column.related_to.id :}}
LEFT JOIN {{: class.status :}} s ON t.status = s.id
LEFT JOIN {{: class.status_group :}} sg ON s.status_group = sg.id
LEFT JOIN person p ON t.assigned_to = p.id

We will get query with more columns now.