1. Queries to columns with multiple references

    Artikel: AN0001882Aktualisiert: 28.06.2021
    Die vorgegebene Sprachenversion vom Artikeltext wird angezeigt, weil es kein Text von der ausgewählten Sprache und Version gibt.

    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

×