Relations

Related Pages

Developer page

Template manager page

Relation types

The template manager supports three types of relations between tables:

1. Parent Relationships
2. 1:n Relationships
3. m:n Relationships

The terms "relations" and "relationships" are used interchangeably.

Parent Relation

A parent relation is a special type of a one-to-many relation (see below), but is assigned differently in the mDIS User Interface. To create a parent relation in the template manager, you just select the "Parent Table" in the pane on the left to the columns.
As a consequence, these are automatically created:

• a column that holds the id of the parent record,
• a database index on this column
• a one-to-many relation between this column and the parent id column .

Parent relations are also used to create the hierarchical filters at the top of every form.

An example of a parent relation is the one between CoreSectionSplit and CoreSection. CoreSectionSplit contains a column section_id; as the name implies, this column contains the id of the parent record.

See figure below.

Declare a Parent Relationship here

One-to-many Relation (1:n)

Similar to the Parent relation, a record can be assigned exactly to one record of a different table. In the example above, CoreSectionSplit would be called the "many" (or "n") side of the relation. CoreSection would be called the "one" (or "1") side of the relation. A CoreSection can contain many CoreSectionSplits, but each CoreSectionSplit must belong to exacly one CoreSection.
As mentioned, an additional column is created on the "many" side of the relation that contains the id of the related record on the "one" side. In the figure above this column is called section_id. It is a foreign key to the id column of the CoreSection table.

Even though the name of the relation is "One-to-many", you always create the relation from the "many" side.

Declare a 1:n Relationship here

Specify details of 1:n relationship

1. In the relation pane, click on the button "Add relation"
2. Select "One-to-many" as the relation type. Be sure to be on the "many" side of the relation.
3. Select the related model (the "one" side of the relation).
4. Select the column of the related model to use i.e. in select inputs
5. Enter the name of the column that will contain the id of the related record (of the "one" side). The column usually should be named according to this convention: <purpose>_<related-model-name>_id¹.

When you click button "OK", the column containing the id of the related record and an index will be created inside the database. PHP files will also be generated.

Example

As another example, try to build a relation between Organisations and Expeditions: Every Expedition should have none or exactly one Organisation.

1. Open ProjectExpedition in the template manager, add a One-to-many relation. Select ContactOrganisation as the related model.
2. Select organsation_acronym as the column to display the related record.
3. Enter organisation as the "Column containing the id of the related record". Click "OK".
4. Regenerate a form that uses this table/model and this relation.
5. Open the form you just regenerated. In that form of model ProjectExpedition you can now use the column organisation to add a select input.

Many-to-many Relation (n:m)

Many-to-many relations are used to assign multiple records of table A to one record of table B, and multiple records of table B to one record of table A.

As an example, think of the assignments of scientists to expeditions. An expedition comprises many scientists. But every scientist take part in multiple expeditions.

In a relational database this type of relation can only be implemented using an additional connection table between the related tables. This separate table stores pairs of ids of the two related tables in every record.

TODO

• Insert schematic of a many-to-many relation here. (3 tables side-by-side, linked with 2 connections)
• Insert screenshot(s) of a many-to-many relation GUI dialogs here.

Worked Example

In the Template Manager:

1. In the relation pane, click on the button "Add relation"
2. Select "Many-to-many" as the relation type.
3. Select the related model.
4. Select the column of the related model to show i.e. in select inputs (Display column of the related model)
5. Enter the name of a pseudo column to access the related records. The column usually should be named <purpose>_<related-model-name>s¹.
6. Enter the name of a pseudo column in the related model to access the records in this model. The column usually should be named <purpose>_<model-name>s¹.

When you click "ok", the pseudo column and the connection table is created in the database. That connection table does not show up in the template manager.

For a many-to-many relation the opposite relation is also created. That is why you have to enter the "Pseudo column in related model to access the records in this model".

To use that relation in the related model and its form, you have to open that model in the template manager, and "Save & Generate" the files.

Usage of Relations

Backend

In the PHP backend, a relation is created in the base model file. Have a look at the @property lines at the top of that generated PHP file. In the one-to-many example this relation is named organisation1n. For the many-to-many example above, the relation is named contactPersonNm.

You can use the relations to directly access the related objects everywhere you can use PHP, i.e. in pseudo fields in the template manager (pseudo columns), in reports, ...

Frontend

In the frontend, related objects are accessible with JavaSript.

The column that connects the related record (in case of a one-to-many relation the column containing the id of the related record, in case of a many-to-many relation the pseudo column) does not contain the id (or ids) of the related records but the objects with all their attributes.

Additionally, all the hierarchically related objects are accessible via their id fields (used for the hierarchical filters). For example, in the form core (for data model CoreCore), the objects in the hierarchy can be accessed using the attributes hole_id, site_id, expedition_id, site_id.

To find all the available objects and their names, have a look a the model's base PHP file located in backend/models/base/ and its method public function fields(). All fields that contain a call to injectUUid() are also provided as JavaScript objects.

To access some of the attributes of ther hierarchically related objects in the form core, write:

• this.hole_id.hole_name
• this.site_id.type_drilling_location
• this.expedition_id.exp_acronym

If you have additional relations, you can access these, too. For additional relations, first verify that an object is available before accessing its attributes.

• this.other_related_object_id.name would result in an error, if this.other_related_object_id is null, because no related record was assigned.

Footnotes:

[1]: <purpose> is required to allow users to quickly identify and its pupose, i.e. what it is used for (e.g. contact for a contact person or chief_scientist for the an expedition's chief scientist).