1. Queries to columns with multiple references

    Article: AN0001882Updated:

    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 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

×