Create a Data Service Query with an Explicit Join and Conditions Within Joins for the Same Table Multiple Times

Run queries with explicit joins and conditions within joins for the same table multiple times.

Example Explicit Join and Conditions Within Joins for the Same Table Multiple Times

{
    "name": "Test Query",
    "tables": [
        {
            "tableName": "PROJECT",
            "columns": [
                "OBJECTID",
                "ENABLEPUBLICATION"
            ],
            "joinedTables": [
                {
                    "tableName": "UNIFIER__INVOICES",
                    "columns": [
                        "INV_ID",
                        "INV_TITLE"
                    ],
                    "joinCondition": {
                        "columnName1": "PROJECT.OBJECTID",
                        "columnName2": "UNIFIER__INVOICES.PROJECT_ID"
                    },
                    "condition": {
                        "operator": "AND",
                        "conditions": [
                            {
                                "columnName": "INV_TITLE",
                                "operator": "EQUALS",
                                "value1": "Schematic Design - January"
                            }
                        ]
                    }
                },
                {
                    "tableName": "UNIFIER__LESSONS_LEARNED",
                    "columns": [
                        "TITLE",
                        "PROJECT_ID"
                    ],
                    "joinCondition": {
                        "columnName1": "UNIFIER__LESSONS_LEARNED.PROJECT_ID",
                        "columnName2": "PROJECT.OBJECTID"
                    },
                    "condition": {
                        "operator": "AND",
                        "conditions": [
                            {
                                "columnName": "RECORD_NO",
                                "operator": "EQUALS",
                                "value1": "LL-001"
                            }
                        ]
                    }
                },
                {
                    "tableName": "UNIFIER__LESSONS_LEARNED",
                    "columns": [
                        "TITLE",
                        "ISSUES",
                        "PROJECT_ID"
                    ],
                    "joinCondition": {
                        "columnName1": "UNIFIER__LESSONS_LEARNED.PROJECT_ID",
                        "columnName2": "PROJECT.OBJECTID"
                    },
                    "condition": {
                        "operator": "AND",
                        "conditions": [
                            {
                                "columnName": "RECORD_NO",
                                "operator": "EQUALS",
                                "value1": "LL-002"
                            }
                        ]
                    }
                },
                {
                    "tableName": "UNIFIER__LESSONS_LEARNED",
                    "columns": [
                        "TITLE",
                        "PROJECT_ID"
                    ],
                    "joinCondition": {
                        "columnName1": "UNIFIER__LESSONS_LEARNED.PROJECT_ID",
                        "columnName2": "PROJECT.OBJECTID"
                    },
                    "condition": {
                        "operator": "AND",
                        "conditions": [
                            {
                                "columnName": "RECORD_NO",
                                "operator": "EQUALS",
                                "value1": "LL-003"
                            }
                        ]
                    }
                }
            ]
        }
    ]
}

To differentiate the columns in the response payload for each occurrence of the same table being joined, a suffix is appended to the response key of the column requested. This suffix will start from 1 (for the second occurrence of the table) and will be incremented by 1 (after second occurrence) for each occurrence of the same table being joined.

Example

In the example request, the table 'UNIFIER__LESSONS_LEARNED' is joined three times with conditions and a different set of columns.

Since the table 'UNIFIER__LESSONS_LEARNED' is joined for the second time in the example request with columns "TITLE", "ISSUES", "PROJECT_ID" being requested, the response will give back the value for the columns with the suffix ".1" appended after the JSON key (which is table_name.column_name), such as UNIFIER__LESSONS_LEARNED.TITLE.1, UNIFIER__LESSONS_LEARNED.ISSUES.1, UNIFIER__LESSONS_LEARNED.PROJECT_ID.1

For the third occurrence of the table 'UNIFIER__LESSONS_LEARNED' being joined, the suffix ".2" has been appended after the JSON key.

Sample Response

{
    "data": {
        "PROJECT": [
            {
                "PROJECT.OBJECTID": "1012",
                "PROJECT.ENABLEPUBLICATION": "Y",
                "UNIFIER__INVOICES.INV_ID": "6",
                "UNIFIER__INVOICES.INV_TITLE": "Schematic Design - January",
                "UNIFIER__LESSONS_LEARNED.TITLE": "Construction Site Procedures",
                "UNIFIER__LESSONS_LEARNED.PROJECT_ID": "1012",
                "UNIFIER__LESSONS_LEARNED.ISSUES.1": "Accidents on the construction site lead to significant delays and increased costs. Days away from work due to occupational injuries alone increased the cost by over $30,000 on this project.",
                "UNIFIER__LESSONS_LEARNED.TITLE.1": "Improved Safety Procedures",
                "UNIFIER__LESSONS_LEARNED.PROJECT_ID.1": "1012",
                "UNIFIER__LESSONS_LEARNED.TITLE.2": "Quality Procedures",
                "UNIFIER__LESSONS_LEARNED.PROJECT_ID.2": "1012"
            }
        ],
        "pagination": [
            {
                "nextTableName": "-1",
                "nextKey": "0"
            }
        ],
        "safetyDate": [
            {
                "queryName": "Test Query",
                "sinceDate": "2022-06-24 10:30:05 +00:00"
            }
        ]
    }
}