1. Queries to columns with multiple references

    Article: AN0001882Updated: 28.06.2021

    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

×