Queries to columns with multiple references

When querying multiple reference columns it is necessary to use this syntax:

Let`s have class project, which refers to class CI in the column add-ci. We want to display project id, project name and short description of configuration items added bz the project. We have to refer to the class project:add-ci, that is created automatically by the system when using multiple reference column (M:N) and its code is in form name of referring class:name of referred class. There are two columns in the class – DataRowId and LinkDataRowId. Column DataRowId is a link to the referring class project, column LinkDataRowId is a link to the referred class added-ci.

Note: Relations and entity in red are created automatically by the system, not by the user.

SELECT p.id AS Project_ID, p.name, c.shortdescription AS SD FROM {{: class.project :}} p
INNER JOIN {{: class.project:added-ci :}} pc ON pc.DataRowId = p.id
INNER JOIN {{: class.ci :}} c ON c.id = pc.LinkDataRowId

In certain cases, we may prefer to write referenced records separated by comma into a plain text field. We can do that by means of clause STUFF:

SELECT p.id, STUFF(
(SELECT ',** ' + c.shortdescription AS [text()]
FROM DataRow{{:class.project.id:}}_{{:class.ci.id:}}Link ref
INNER JOIN {{:class.ci:}} c ON c.id = ref.LinkReferentionId
WHERE ref.ReferentionId = p.Id AND ref.ColumnClassDefId = {{:class.project.column.added-ci.id:}}
ORDER BY c.shortdescription
For XML PATH ('')
), 1, 1, ''
) added_ci

FROM {{:class.project:}} p WHERE p.deleted IS NULL

In case if multiple reference to a user, it is necessary to use class person, that contains users of ObjectGears. Let`s have class project which contains column users, that is a multiple reference to ObjectGears user. Users are contained in the table person. We have to refer to it bz a simple stating its name (we do not place it to double brackets and colons like names of classes created by users). Association class project:uzivatele which is generated by the system has columns DataRowId (it is a link to the class project) and column PersonId (it is a link to the table of users).

Note: Relations and entity in red are created automatically by the system, not by the user.

SELECT p.id AS Project_ID, p.code, per.code AS Persons
FROM {{: class.project :}} p
INNER JOIN {{: class.project:person :}} pp ON pp.DataRowId = p.id
INNER JOIN person per ON per.id = pp.PersonId