Queries involving joined tables can return results in a hierarchy.
The core.executeQuery command can also be used in joinTables mode. In this mode, you specify only the tables you want joined. The results of the query are used as keys to perform a subquery for all related results from the joined table. The joined table creates a hierarchical result set that could contain nested results. The nested results level depends on how many join tables are specified.
To join tables using core.executeQuery, you specify a comma-separated list of tables to join as the joinTables parameter. You do not specify a select parameter when joining tables. The results are returned as a result hierarchy with each record of the parent table becoming the parent node of the related records in each child table. The hierarchy continues until all joined tables are displayed.
Example
This example executes a simple join between the OrionTaskLog and the OrionTaskLogMessage tables.
OK: <?xml version="1.0" encoding="UTF-8"?> <result> <list> <row> <OrionTaskLogTask.Name>New Task</OrionTaskLogTask.Name> <OrionTaskLogTask.StartDate>2010-11-23T13:01:37-08:00</OrionTaskLogTask.StartDate> <OrionTaskLogTask.EndDate>2010-11-23T13:01:37-08:00</OrionTaskLogTask.EndDate> <OrionTaskLogTask.UserName>ga</OrionTaskLogTask.UserName> <OrionTaskLogTask.Status>0</OrionTaskLogTask.Status> <OrionTaskLogTask.TaskSource>scheduler</OrionTaskLogTask.TaskSource> <OrionTaskLogTask.Duration>493</OrionTaskLogTask.Duration> <OrionTaskLogTaskMessage> <list> <row> <OrionTaskLogTaskMessage.Message>Purge audit log</OrionTaskLogTaskMessage.Message> </row> <row> <OrionTaskLogTaskMessage.Message>Purge audit log (Purge log records older than: 1 days)</OrionTaskLogTaskMessage.Message> </row> </list> </OrionTaskLogTaskMessage> </row> </list> </result>
This output shows the core.executeQuery command returned each top-level object (the OrionTaskLogTask record) and its two associated message records. The output is shown as XML to highlight the hierarchical arrangement of the results.