Relation

Overview

A relation between two database tables refers to how data in one table is related to data in another table, it is an object from the datalayer that provides the link from one datasource to another, based on one or more matches between the dataproviders in the datasource and an operator. These relationships are established using keys, typically primary keys and foreign keys.

Property Summary

Properties Details

allowCreationRelatedRecords

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

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.

Flag that tells if related records can be created through this relation.

The default value of this flag is "false".

Type Boolean

Sample

"true" or "false"

allowParentDeleteWhenHavingRelatedRecords

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 disabled this option. An attempt to delete a customer record will fail, if that customer has one or more orders.

Flag that tells if the parent record can be deleted while it has related records.

The default value of this flag is "true".

Type Boolean

Sample

"true" or "false"

comment

Additional information, such as programmer notes about the relation's purpose.

Type String

Sample

"gets order details table data starting from orders table"

deleteRelatedRecords

This option specifies that records in a related foundset can be 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 related order records to be deleted.

Flag that tells if related records should be deleted or not when a parent record is deleted.

The default value of this flag is "false".

Type Boolean

Sample

"true" or "false"

deprecated

A relation can be deprecated, and a description has to be provided to hint users about what the alternative is.

Type String the deprecate info for this element or null if it is not deprecated

Sample

"not used anymore"

encapsulation

A relation has encapsulation property, similar to the form encapsulation property. The following can be used/checked:

  • Public – accessible from everywhere

  • Hide in Scripting; Module Scope – code completion is disabled for the relation, and it is accessible only from the module that it was created in

  • Module Scope – accessible from the module it was created in

For non-public encapsulation, if the relation is accessed from somewhere else, you get a build marker in Problems View, but it will still function properly.

Type Number the encapsulation mode/level of the persist.

Sample

"Public"

foreignDataSource

Qualified name of the foreign data source. Contains both the name of the foreign server and the name of the foreign table. It is selectable from all available tables and has this format: "server-name.table-name".

Can be any database table or view from any named server connection and is not limited to the same database as the destination table.

At runtime, a related foundset will contain records from the destination table.

The destination table can exist in a separate database from 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

Sample

'example_data.order_details'

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. A String which specified a set of sort options for the initial sorting of data retrieved through this relation. Has the form "column_name asc, another_column_name desc, ...".

Type String

Sample

"productid asc"

joinType

The join type that is performed between the primary table and the foreign table. Can be "inner join" or "left outer join".

Type Number

Sample

"left outer join"

A relation can specify one of two SQL Join Types. A SQL join 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.

Example: Assume that the user chooses to sort a customer list containing 50 records. The sort is based on the account manager's last name, which is in the employees table. However, 3 of the customers don't have an employee listed to manage the account.

foundset.sort('customers_to_employees.last_name asc');
foundset.getSize(); //  returns 50 if the customers_to_employees relation specifies left outer join, 47 if the relation specifies inner join.

name

The name of the relation.

Type String

Sample

"customer_to_orders"

primaryDataSource

Qualified name of the primary data source. Contains both the name of the primary server and the name of the primary table. Can be any database table or view from any named server connection.

At runtime, a related foundset will exist in the context of a single record from the source table. For example, the relation customer_to_orders, will become available in the context of any record in a foundset which is based on the customers table.

Type String

Sample

'example_data.customer'

Last updated