One-To-Many Links

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:

InfoAsset Manager – Collection Network

From	To	Name
Assets	General Maintenance	general_maintenance_records
	General Survey	general_surveys
	General Survey Line	general_survey_lines
	Tasks	tasks
	User Defined Task Only	user_tasks
	Water Quality Survey	water_quality_surveys
	Work Package	work_packages
Channel	Cross Section Survey	cross_section_surveys
Data Logger	Monitoring Survey	monitoring_surveys
Defense Structure	Flood Defence Survey	flood_defence_surveys
Link	Link	ds_links
Link	Link	us_links
Manhole	Drain Test	drain_tests
	General Incident	incidents
	GPS Survey	gps_surveys
	Manhole Repair	manhole_repairs
	Manhole Survey	manhole_surveys
	Monitoring Survey	monitoring_surveys
Materials	Orders	orders
Node	Link	ds_links
Node	Link	us_links
Node/Pipe/Connection Pipe	Name Group	asset_name_groups
Pipe	Acoustic Survey	acoustic_surveys
	Acoustic Survey	acoustic_surveys
	CCTV Survey	cctv_surveys
	Dye Test	dye_tests
	General Incident	incidents
	Monitoring Survey	monitoring_surveys
	Pipe Clean	pipe_cleans
	Pipe Repair	pipe_repairs
	Property	properties
	Smoke Test	smoke_tests
Pipe Clean	Pipe	joined_pipes
Property	FOG Inspection	fog_inspections
Property	Incident	incidents
Pump Station	Electrical Maintenance	electrical_maintenance_records
	Generator	generators
	Mechanical Maintenance	mechanical_maintenance_records
	Pump	pumps
	Pump Station Survey	pump_station_surveys
Smoke Test	Smoke Defects	smoke_defects
Tasks	Resource	resources
Tasks/Orders	Material	materials

InfoAsset Manager – Distribution Network

From	To	Name
Assets	General Maintenance	general_maintenance_records
	General Survey	general_surveys
	General Survey Line	general_survey_lines
	Tasks	tasks
	User Defined Task Only	user_tasks
	Water Quality Survey	water_quality_surveys
	Work Package	work_packages
Data Logger	Monitoring Survey	monitoring_surveys
Hydrant	Hydrant Test	hydrant_tests
Link	Link	ds_links
Link	Link	us_links
Manhole	GPS Survey	gps_surveys
	Manhole Repair	manhole_repairs
	Manhole Survey	manhole_surveys
Materials	Orders	orders
Meter	Meter Maintenance	maintenance_records
Meter	Meter Test	meter_tests
Node	GPS Survey	gps_surveys
	Incident	incidents
	Link	ds_links
	Link	us_links
	Monitoring Survey	monitoring_surveys
Pipe	Pipe Repair	pipe_repairs
Pipe	Pipe Sample	pipe_samples
Property	Incident	incidents
Property	Meter	meters
Pump Station	Electrical Maintenance	electrical_maintenance_records
	Generator	generators
	Mechanical Maintenance	mechanical_maintenance_records
	Pump	pumps
	Pump Station Survey	pump_station_surveys
Tasks	Resource	resources
Tasks/Orders	Material	materials
Valve	Valve Maintenance	maintenance_records
Valve	Valve Shutoff	valve_shut_offs
Valve Shutoff	Valve	valves

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.