11 One-To-Many Links 🌐
One-to-many links enable queries to consider linked objects, even when there's a possibility of having none, one, or multiple linked objects.
The keywords all_us_links and all_ds_links help in joining ALL upstream or downstream links to any node or link type.
Available One-To-Many Links:
| 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 |
🔍 How to Use them in Queries:
11.1 Determining Linked Objects 🔗
Utilize ANY(linkname.*) to check if linked objects exist.
Example: ANY(us_links.*)
11.2 Counting Linked Objects 🧮
Employ COUNT(linkname.*) to tally the linked objects.
For instance: COUNT(manhole_surveys.*)=0
11.3 Aggregate Functions 📊
Almost all aggregate functions from the array fields section, except FIRST and LAST, apply for one-to-many links.
11.4 'Bare' One-To-Many Fields Usage 📋
You can assign values to one-to-many fields through an assignment clause outside an aggregate function. For example:
- With only a SET sub-clause:
SET us_pipes.width = 123sets the width for every upstream pipe. - With a WHERE sub-clause:
SET us_links.width = 234 WHERE shaft_depth = 1650will adjust the width for every upstream link where the shaft depth equals 1650.
11.5 Interplay Between 'Bare' Fields & Aggregate Functions ⚙️
If one-to-many field values are used 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 where the WHERE sub-clause holds true.
However, if you incorporate an aggregate function IN the WHERE sub-clause, it will run for all objects linked via the one-to-many link to the current object.
