One to many links allow queries to include linked objects where there may be zero, one or many linked objects.
The keywords all_us_links and all_ds_links will allow the joining of ALL upstream of downstream links to any node or link type.
The available one to many links are as follows:
| From | To | Name |
|---|---|---|
| Node | Link | us_links |
| Node | Link | ds_links |
| Node | Subcatchment | subcatchments |
| Link | Link | us_links |
| Link | Link | ds_links |
| Subcatchment | Lateral link | drains_from |
| Subcatchment | Lateral link | lateral_connected_links |
They may be used in queries in a very similar manner to array fields, as follows:
Determining if there are any linked objects
ANY(linkname.*) may be used to determine if there are any linked objects e.g. ANY(us_links.*)
Counting the number of linked objects
COUNT(linkname.*) may be used to count the number of linked objects e.g COUNT(manhole_surveys.*)=0
Aggregate functions
The aggregate functions listed for array fields with the exception of FIRST and LAST are available for one-to-many links.
Use of ‘bare’ one-to-many fields
It is possible to set values in one-to-many fields by using them in an assignment clause outside an aggregate function.
If there is only a SET sub-clause with no WHERE sub-clause, the SET sub-clause will be run for every object related to every object in the table by the one to many link e.g.
SET us_pipes.width = 123 will set the width for every upstream pipe.
If there is a WHERE sub-clause that does not include a reference to the one-to-many field, the SET sub-clause will be run for every record in the one-to-many for every object for which the WHERE sub-clause is true e.g.
SET us_links.width = 234 WHERE shaft_depth = 1650 will set the width for every upstream link to 234 where the shaft depth is 1650.
If there is a WHERE sub-clause that includes a reference to the one-to-many field, then the SET sub-clause will be run for every object linked via the one-to-many field for which the WHERE sub-clause is true.
Interaction between ‘bare’ one-to-many fields and aggregate functions
If you use one-to-many field values outside aggregate functions in a WHERE sub-clause, and inside aggregate functions in the SET sub-clause, the aggregate functions in the SET sub-clause will only be evaluated for objects linked to the current object via the one-to-many field for which the WHERE sub-clause is true.
If, however, you use an aggregate function IN the WHERE sub-clause, the aggregate function will be run for all objects linked to the current object via the one-to-many link.