Relations

A relation expresses the potential connection between records in Cloud SQL models. It represents the ability for one record to be associated with one or more other records. For example, a Person model in social app could have a Friends relation that represents friendship between two people.

Relation basics

Relations in App Maker are bidirectional. Each direction is called a relation end, and each relation end has two properties:

Name—The relation end's name, used to refer to the relation in data binding and scripts.
Count—The number of connections the relation end supports. Count can be set to one, which means the record accepts only one connection, or many, which means the record accepts unlimited connections. Relations can be one-to-one, one-to-many, and many-to-many. The value of count-to-count is called the cardinality or the degree of the relationship.

For example, an HR app can use relations between EmployeeDB and Teams models to represent employees and their teams. You can create a many-to-many relation between EmployeeDB and Teams because each employee can be a member of many teams. The relation ends have the following properties:

Name=Members, Count=Many
Name=Teams, Count=Many

You can also create a one-to-many relation between EmployeeDB and Teams because each team can have one manager and managers can have multiple teams. The relation ends have the following properties:

Name=Manager, Count=One
Name=Teams, Count=Many

Create and edit relations

To create a relation:

Click a Cloud SQL model and go to its Relations tab.

To create a relation, click Add Relation. Use the creation wizard to:

Set the target model for the other relation end.

Select the count for each relation end.

(Optional) Enter names for each relation end if you don't want to keep the defaults.

(Optional) For one-to-one and one-to-many relations, select Owner for a relation end with a count of one. Specify an Owner model in a relation only if:

Owned records shouldn't exist without their owner records, and

Owned records should be automatically deleted when the owner record is deleted. Learn more about ownership of associated records.
Click Create.

After you create a relation, you can edit the name of the relation end. For relation ends with a count of one, you can enable or disable ownership. For relation ends with a count of many, you can change how it is sorted.

To edit a relation:

Click a model and go to its Relations tab.
In the list of relations, click the relation end name.

For relations that connect records in two models, the relation list for each model only displays the relation end name of the opposite model. For relations that connect records in the same model, both relation ends are listed. In the EmployeeDB model example, both Manager and Members are listed on the Relations tab.
Edit the name, owner, or sorting. Your changes are saved automatically.

To set sorting, select which field to use to sort records. Select the Ascending checkbox to set the sorting order of records for the relation end. If you don't specify a sorting preference, the model's data backend determines the sorting order.

Ownership of associated records

For relations that have at least one relation end with a count of one, you can specify an Owner, subject to some rules. When you set an owner, you create an owner-owned relationship for associated records in the two models:

With ownership—When a user or app logic deletes a record from the owner model, the app deletes all associated owned records. When an owned record is deleted, owner records aren't deleted.
Without ownership—When a record on either end of the relation is deleted, no records are deleted on the other end.

Only specify an owner model in a relation if the following conditions are both true:

Owned records shouldn't exist without their owner records
You want records on the owned end of the relation to be deleted automatically when the owner record is deleted.

Ownership example

You might want to delete invoice items when an invoice is deleted. With a one-to-many relation between the Invoice and Item models, make the Invoice model the owner. When a user deletes an Invoice record, the app also deletes all owned Item records—the records that were associated with the deleted Invoice record.

Note that the deleted records are specific instances of items, such as specific disc drives from inventory, not categories of items, such as kinds of disc drives that you stock.

Ownership rules

Only the model on the one side of a one-to-one or one-to-many relation can be an owner.
You can't designate a model as an owner if that would create a cycle in ownership, such as record A owns record B and record B owns record A.
You can chain owner-owned relations. For example, if you have the following models and relations:
- Forum, Topic, and Comment models
- A one-to-many relation from Forum to Topic, with the Forum model as the owner
- A one-to-many relation from Topic to Comment, with the Topic model as the owner
When you delete a Topic record, App Maker also deletes all associated Comment records. When you delete a Forum record, App Maker also deletes all associated Topic records and their associated Comment records.
A model with many relations can be owned by many Owner models.
When you delete a record from an Owner model, App Maker deletes that record's owned records from other models, even if the records are also owned by records in other models.

Manage associations between records

An association is the actual link between two records through a model relation. For example, if you have a one-to-many relation between the Manager and Teams relation ends, there are associations between the record for Manager1 and the records for each of the teams.

You can create and modify associations betweeen records in three ways, depending on your app's workflow:

Use data bindings to let a widget display or change associations for a record.
Use a client script to handle more complicated association changes than data binding can do.
Use a server script to handle bulk changes or make changes securely.

Modify associations with a data binding

There are many ways to change the associations between records through data bindings, but the simplest is to access a relation end as a property of a model datasource's item. In model datasources, relation ends are exposed as record properties, much like fields.

To enable a widget display or change associated records for the currently-selected record, bind the widget's value to @datasource.item.[relation end name]. This method is especially useful for dropdown widgets (when the relation end has a count of one) and multiselect boxes (when the relation end has a count of many). For an example, try the Connect data models tutorial; when you create the UI elements to list employees, you set up bindings between widget values and datasources.

Modify associations with a client script

This approach is more difficult than data bindings because you need to ensure that the datasource has already loaded the record's associated records. You can make sure the associated records are loaded in the following ways:

Enable prefetch for the datasource
Call _loadRelation-name on the record and wait for an asynchronous callback
Wait for the onLoad event of a relation datasource
Wait for the onDataLoad event of a widget that uses a relation datasource

For example, to build on the EmployeeDB-Team relation scenario from earlier, you can use the following code to associate records (assign a manager to a team member and add the team member to the manager's team). To make it easier to access the correct employee records, you've created two datasources in the EmployeeDB model:

Manager, which lets you select a manager
Member, which lets you select a team member

    var managerRecord = app.datasources.Manager.item;
    var teamRecord = app.datasources.Teams.item;

    // Assign the manager to the team.
    teamRecord.Manager = managerRecord;

    // Changes are saved automatically if the datasource in auto-save mode

    // Add a team member to a Manager's team.
    // Note: Retrieve Members on the client before proceeding, such as by using prefetch option in datasource - datasources Team -> Members)
    var engineerRecord = app.datasources.TeamMember.item;
    teamRecord.Members.push(engineerRecord);

Modify associations with a server script

It's easier to modify associations in server scripts than in client scripts because you don't have to worry about when associated records load. Access the appropriate record, then use its relation end properties to associate records through the relation.

For example, to build on the EmployeeDB-Team relation scenario from earlier, you can add managers and members to teams with the following server script:

// Get the record for the Team to modify.
var teamRecord = app.models.Teams.getRecord("team1");

// Assign a manager to the Team.
var managerRecord = app.models.EmployeeDB.getRecord("manager1");
teamRecord.Manager = managerRecord;

// Note: The new association is not saved yet

// Assign a team member to the Team.
var engineerRecord = app.models.EmployeeDB.getRecord("engineer1");
teamRecord.Members.push(engineerRecord);

// Save both changes to the database.
app.saveRecords([teamRecord]);

Relation filtering

To filter or sort records by a field in a related Cloud SQL model, add the relation name or names followed by a field name. For example, to filter a Mother relation by the related records' Age field, use Mother.Age.

You can also apply equals, notEquals, in, and notIn filters on an associated record or record key. For example, to check that a manager's manager is assigned correctly, you could filter by Manager.Manager._key._equals.

Relation filtering is available in data binding and server scripting.