# JSRelation ## Overview `JSRelation` provides a model for defining and managing relations between primary and foreign data sources, supporting operations such as joins, sorting, and criteria management. It includes constants, properties, and methods to facilitate the creation, modification, and use of relational data structures. ## Functionality JSRelation offers constants to define join types (`FULL_JOIN`, `INNER_JOIN`, `LEFT_OUTER_JOIN`, and `RIGHT_OUTER_JOIN`) used in database relations. These constants influence how tables are joined during operations like sorting and querying. Properties such as `allowCreationRelatedRecords` and `deleteRelatedRecords` provide control over cascading actions like record creation or deletion across related data sets. Other attributes like `foreignDataSource`, `primaryDataSource`, and `initialSort` allow fine-tuning of the relational setup, enabling flexibility across multiple databases or data views. The methods provided, such as `getRelationItems` and `getUUID`, allow querying and identifying relations, while methods like `newRelationItem` and `removeRelationItem` enable dynamic modification of relation criteria. For example, relations can be defined between specific columns or modified to add or remove criteria dynamically during runtime. JSRelation supports a robust configuration system with detailed examples, showcasing the integration of parent-child relations and control over operations such as cascading deletions, dynamic key assignments, and data source management. For details please refer to the [Relation](https://docs.servoy.com/reference/servoycore/object-model/solution/relation) section of this documentation ## Constants Summarized | Type | Name | Summary | | ---------------------------------------------------------------------------- | --------------------------------------- | ------------------------------------------ | | [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) | [FULL\_JOIN](#full_join) | Constant for the joinType of a JSRelation. | | [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) | [INNER\_JOIN](#inner_join) | Constant for the joinType of a JSRelation. | | [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) | [LEFT\_OUTER\_JOIN](#left_outer_join) | Constant for the joinType of a JSRelation. | | [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) | [RIGHT\_OUTER\_JOIN](#right_outer_join) | Constant for the joinType of a JSRelation. | ## Properties Summarized | Type | Name | Summary | | ------------------------------------------------------------------------------ | --------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------- | | [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) | [allowCreationRelatedRecords](#allowcreationrelatedrecords) | Flag that tells if related records can be created through this relation, or not. | | [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) | [allowParentDeleteWhenHavingRelatedRecords](#allowparentdeletewhenhavingrelatedrecords) | Flag that tells if the parent record can be deleted while it has related records. | | [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) | [deleteRelatedRecords](#deleterelatedrecords) | Flag that tells if related records (from a related foundset) should be deleted or not when a parent record is deleted. | | [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) | [foreignDataSource](#foreigndatasource) | Qualified name of the foreign data source. | | [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) | [initialSort](#initialsort) | Foundsets, including related foundsets, have a sort property. | | [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) | [joinType](#jointype) | The join type that is performed between the primary table and the foreign table. | | [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) | [name](#name) | The name of the relation. | | [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) | [primaryDataSource](#primarydatasource) | Qualified name of the primary data source. | ## Methods Summarized | Type | Name | Summary | | --------------------------------------------------------------------------------------------------- | ---------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------- | | [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) | [getRelationItems()](#getrelationitems) | Returns an array of JSRelationItem objects representing the relation criteria defined for this relation. | | [UUID](https://docs.servoy.com/reference/servoycore/dev-api/application/uuid) | [getUUID()](#getuuid) | Returns the UUID of the relation object | | [JSRelationItem](https://docs.servoy.com/reference/servoycore/dev-api/solutionmodel/jsrelationitem) | [newRelationItem(dataprovider, operator, foreinColumnName)](#newrelationitem-dataprovider-operator-foreincolumnname) | Creates a new relation item for this relation. | | void | [removeRelationItem(primaryDataProviderID, operator, foreignColumnName)](#removerelationitem-primarydataproviderid-operator-foreigncolumnname) | Removes the desired relation item from the specified relation. | ## Constants Detailed ### FULL\_JOIN Constant for the joinType of a JSRelation. It is also used in solutionModel.newRelation(...). **Type**\ [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.joinType = JSRelation.LEFT_OUTER_JOIN; ``` ### INNER\_JOIN Constant for the joinType of a JSRelation. It is also used in solutionModel.newRelation(...). **Type**\ [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.joinType = JSRelation.LEFT_OUTER_JOIN; ``` ### LEFT\_OUTER\_JOIN Constant for the joinType of a JSRelation. It is also used in solutionModel.newRelation(...). **Type**\ [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.joinType = JSRelation.LEFT_OUTER_JOIN; ``` ### RIGHT\_OUTER\_JOIN Constant for the joinType of a JSRelation. It is also used in solutionModel.newRelation(...). **Type**\ [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.joinType = JSRelation.LEFT_OUTER_JOIN; ``` ## Properties Detailed ### allowCreationRelatedRecords Flag that tells if related records can be created through this relation, or not. This option is enabled by default, and it specifies that records can be created within a related foundset. Moreover, when records are created in a related foundset, the key columns in the new record may be automatically filled with the corresponding values from the source record. **Example**: Assume a relation, \_customers\_to\_orders\_ defined by a single key expression, *customers.customerid = orders.customerid* ```js customerid; // 123, the customer's id customers_to_orders.newRecord();// create the new record customers_to_orders.customerid; // 123, the order record's foreign key is auto-filled ``` Key columns will be auto-filled for expressions using the following operators: * \= * \#= * ^||= If this option is disabled, then records cannot be created in a related foundset. If attempted, a \[ServoyException]\() is raised with the error code, \[NO\_RELATED\_CREATE\_ACCESS]\(). **Type**\ [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) true if related records can be created, false otherwise. **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.allowCreationRelatedRecords = true; ``` ### allowParentDeleteWhenHavingRelatedRecords Flag that tells if the parent record can be deleted while it has related records.\ This option is enabled by default. When disabled, it will prevent the deleting of a record from the source table if the related foundset contains one or more records. If the delete fails, a \[ServoyException]\() is raised with the error code, \[NO\_PARENT\_DELETE\_WITH\_RELATED\_RECORDS]\(). **Example**: Assume the relation customers\_to\_orders has this option disabled. An attempt to delete a customer record will fail, if that customer has one or more orders. **Type**\ [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) true if parent records can be deleted when having related records, false otherwise. **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.allowParentDeleteWhenHavingRelatedRecords = false; ``` ### deleteRelatedRecords Flag that tells if related records (from a related foundset) should be deleted or not when a parent record is deleted.\ Moreover, it also enforces a cascading delete, such that when a source record is deleted, all records in the related foundset will also be deleted, eliminating the possibility of orphaned records. **Example**: Assume the relation customers\_to\_orders has enabled this option. The deleting of the customer record will cause all of the related order records to be deleted. The default value of this flag is "false". **Type**\ [Boolean](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/boolean) true if related records are deleted when the parent record is deleted, false otherwise. **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.deleteRelatedRecords = true; ``` ### foreignDataSource Qualified name of the foreign data source. Contains both the name of the foreign server and the name of the foreign table. It can be chosen from all of the available tables and has this format: "server-name.table-name".\ It can be any database table or view from any named server connection; it is not limited to the same database as the destination table. At runtime, a related foundset will contain records from the destination table. NOTE: The destination table can be from a separate database than the source table. This is a powerful feature, but it is worth noting that a related foundset who's relation is defined across two databases will not be available when the source foundset is in find mode. This is because a related find requires a SQL JOIN, which cannot be issued across databases for all vendors. **Type**\ [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) the data source URI of the foreign table. **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.primaryDataSource = 'db:/user_data/another_parent_table'; relation.foreignDataSource = 'db:/user_data/another_child_table'; ``` ### initialSort Foundsets, including related foundsets, have a sort property. By default, any foundset is sorted by the primary key(s) of the table upon which it is based. Relations have an Initial Sort property, which overrides the default sort, such that any related foundset is initialized to use the sorting definition defined by the relation object.\ For more information see foundset sorting. The value looks like "column\_name asc, another\_column\_name desc, ...". **Type**\ [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) the initial sort string for the relation. **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.initialSort = 'another_child_table_text asc'; ``` ### joinType The join type that is performed between the primary table and the foreign table.\ Can be "inner join" or "left outer join". This SQL join is used when a **find** or a **sort** is performed using related criteria, and thus the join type will affect behavior in these situations. * **Inner Join** - SQL Inner Join does not return any rows for parent records which have no related records. Therefore, if a sort or a find is performed when a related data provider is used for criterion, the related foundset may have records omitted due parents with no child records. * **Left Outer Join** - SQL Left Outer Join will return always return a row for the parent record even if there are no related records. Therefore, if a sort or a find is performed when a related data provider is used for a criterion, the related foundset will include all matching records, regardless of the presence of related records. **Type**\ [Number](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/number) the join type for the relation, represented as an integer. **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.joinType = JSRelation.LEFT_OUTER_JOIN; ``` ### name The name of the relation. **Type**\ [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) the name of the relation. **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.name = 'anotherName'; var firstTab = tabs.newTab('firstTab', 'Child Form', childForm, relation); firstTab.relationName = relation.name; ``` ### primaryDataSource Qualified name of the primary data source. Contains both the name of the primary server and the name of the primary table. **Type**\ [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) the data source URI of the primary table. **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.primaryDataSource = 'db:/user_data/another_parent_table'; relation.foreignDataSource = 'db:/user_data/another_child_table'; ``` ## Methods Detailed ### getRelationItems() Returns an array of JSRelationItem objects representing the relation criteria defined for this relation. **Returns:** [Array](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/array) An array of JSRelationItem instances representing the relation criteria of this relation. **Sample** ```js var criteria = relation.getRelationItems(); for (var i=0; i' '<') must be provided. **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **dataprovider** The name of the primary dataprovider. * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **operator** The operator used to relate the primary and the foreign dataproviders. * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **foreinColumnName** The name of the foreign dataprovider. **Returns:** [JSRelationItem](https://docs.servoy.com/reference/servoycore/dev-api/solutionmodel/jsrelationitem) A JSRelationItem instance representing the newly added relation item. **Sample** ```js var relation = solutionModel.newRelation('parentToChild', 'db:/example_data/parent_table', 'db:/example_data/child_table', JSRelation.INNER_JOIN); relation.newRelationItem('another_parent_table_id', '=', 'another_child_table_parent_id'); // for literals use a prefix relation.newRelationItem(JSRelationItem.LITERAL_PREFIX + "'hello'",'=', 'mytextfield'); ``` ### removeRelationItem(primaryDataProviderID, operator, foreignColumnName) Removes the desired relation item from the specified relation. **Parameters** * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **primaryDataProviderID** the primary data provider (column) name * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **operator** the operator * [String](https://docs.servoy.com/reference/servoycore/dev-api/js-lib/string) **foreignColumnName** the foreign column name **Returns:** void **Sample** ```js var relation = solutionModel.newRelation('myRelation', 'db:/myServer/parentTable', 'db:/myServer/childTable', JSRelation.INNER_JOIN); relation.newRelationItem('someColumn1', '=', 'someColumn2'); relation.newRelationItem('anotherColumn', '=', 'someOtherColumn'); relation.removeRelationItem('someColumn1', '=', 'someColumn2'); var criteria = relation.getRelationItems(); for (var i = 0; i < criteria.length; i++) { var item = criteria[i]; application.output('primary column: ' + item.primaryDataProviderID); application.output('operator: ' + item.operator); application.output('foreign column: ' + item.foreignColumnName); } ``` ***