Entity relationships

Entity relationship is a vital part of the Object-Relational Mapping (ORM) framework. It allows you to define the relationship between records in different database tables using methods on entity classes. There are a few relation types available and more to come if needed. The advantage of having relation is that it hides behind the scenes tedious operations, for example doing an array map and injecting a related entity.

Defining relationships

There are a number of relationships that be defined, which will be explored in more detail on this page. These are:

has_one: A one-to-one link between two tables
has_many: A one-to-many link between tables
has_many_through: Connects two entities which don't have a direct reference, but are referenced using an intermediate table (relates many entities)
has_one_through: Connects two entities which don't have a direct reference, but are referenced using an intermediate table (relates one entity)
belongs_to: The inverse relation of has_one and has_many

has_one

has_one relation defines a one-to-one link between two tables. It's very similar to has_many however it expects only one related model to exist (it can be more than one, it would however return you only the first one). In Totara we don't have many examples of true one-to-one relationships, perhaps an example of that would be custom field tables: info_data table has one corresponding entry in info_data_param. To define a has one relation we'd use the following (for example the first table with custom fields is used):

Has_one - definition

class comp_type_info_data extends entity {

    public const TABLE = 'comp_type_info_data';

    /**
     * Param
     *
     * @return has_one
     */
    public function param(): has_one {
		// This method accepts another argument - key, which defaults to id and will be true in most cases.
        return $this->has_one(comp_type_info_data_param::class, 'dataid'); 
    }
}

class comp_type_info_data_param extends entity {
    public const TABLE = 'comp_type_info_data_param';
}

You can name your relation the way you want it, however it is recommended to name it meaningfully.

Naming clash

You should avoid naming clashes when defining your relation. You won't be able to name it using a name of a method that already exists on the model, and you shouldn't make a name of a relation matching a property that already exists on the entity. For example, if your entity has a property type (underlying table has a field type) you shouldn't name a relation type as it will cause warnings to appear. Considering a real-life application it is possible, however improbable. This is due to inconsistencies in naming that we have, for example:

Activity belongs to a course and logically you would name the corresponding relation course. In our case the column representing a key might be named course as well, this is quite rare though as usually the column would be named course_id so it wouldn't cause problems.

To query your relationship on an entity you might use the following syntax:

Has_one - query

$entity = comp_type_info_data::repository()
			->order_by('id')
			->first();

// This will load the related entity - or null if it does not exist
$entity->param()->one();

Alternatively, you can use the property syntax:

Has_one - query

$entity = comp_type_info_data::repository()
			->order_by('id')
			->first();

// This will load the related entity if it hasn't been and will cache the results on the model.
$entity->param;

Example

Consider an example with the following:

An entity named client (the main entity)
A model named client
An entity named client_settings (the related entity)
A model named client_settings

The entity and model need to be set up.

We want, when we have a main model (client), to be able to fetch its related client_settings model, e.g. by calling:

$client_settings_model_related = $model_client->client_settings;

Entity class changes:

client Entity class (main entity)

Add a method to say the main entity has a one-on-one relationship to the other entity:

Add an annotation above the entity class, setting a property to access for the related entity:

client_settings Entity class (related entity)

Add a method to say the related entity has a one-on-one relationship to the main entity:

client Model class (main model)

Add an annotation above the model class for the related model:

In the model, when saving the related entity on the main entity, you can save it like this and it will automatically fill in the related entity's ID (the foreign key):

Test

For a test, check you can access the related entity after you have created a main entity with a relation on it, e.g.:

has_many

has_many relation defines a one-to-many link between tables. It behaves in a similar way to as one, however returns a collection of related models. Examples of tables related using has_many relations are everywhere: a course has many course modules, a course has many completions, a user has many course completions, a competency framework has many competencies, a competency may have many child competencies etc. For example we are going to use courses and course modules. To define the relation we'd need to do the following:

Has_many - definition

Essentially, the same naming considerations apply.

Plural name

Also note that it makes sense to pluralise the name of your has_many relation methods. E.g. courses have modules - so the relation would be modules().

Has_many - query

Worth noting that you can apply additional constraints when defining the relation:

Has_many - extra definition constraints

Or aggregated conditions considerations

Be careful adding or_where conditions as it might lead to logical problems, since all the constraints are just appended, or you will need to wrap it in a nested condition to preserve filtering related entities using the key correctly.

has_many_through

has_many_through relation connects two entities which don't have a direct reference, but are referenced using an intermediate table. It behaves similarly to has_many, however when relation is queried it joins an intermediate table which is used to connect these. Examples of tables related using has_many_through relation include competency and scale values (which are connected via scale assignment table).

Has_many_through - definition

To query your relationship on an entity you might use the following syntax:

Has_many_through - query

All other things relative to has_many relation can be applied to has_many_through relations.

has_one_through

has_one_through relation connects two entities which don't have a direct reference, but are referenced using an intermediate table. It works like has_many_through but only relates one entity instead of many. Examples of tables related using has_one_through relation include competency and scales (which are connected via scale assignment table).

Has_many - definition

To query your relationship on an entity you might use the following syntax:

Has_many - query

All other things relative to has_one relation can be applied to has_one_through relations.

belongs_to

belongs_to is the inverse relation of has_one and has_many. The indication of the fact that it is a belongs_to relation is when you define your foreign key right on your entity. For example a course_module belongs to a course as course_modules table has a course field that references a course table. Let's use the examples from the above to define belongs to relation.

Belongs_to (has many inverse) - definition

The inverse of the has_one model will be exactly the same. Querying belongs_to relationship is exactly the same as well as querying has_one/has_many. You have options to access it using a method or a property which will cache the result for subsequent calls.

Property vs. method usage

When querying a relation you have the choice of using the method you implemented to define the relationship or a magic property access with the same name.

Method

When you query the relation using method, not a property (e.g. modules() instead of modules) the query will be executed every time. That gives you power to apply extra constraints when using this syntax as essentially all the repository methods are available directly on the relation.

Example:

Property

By accessing the relation via the magic property the related entity or related entities will be cached the first time you use the property. So for subsequent calls you won't trigger additional queries. On the other hand you cannot add any additional constraints as you are accessing the related entity/entities directly.

This is likely your preferred method as for performance reason you don't want additional queries triggered every time you access the relation.

Also if you use eager loading (see next section) accessing the related entities via the property makes most sense as the entities will already be cached.

Eager-loading relations

To eager load related models you can use with method on the repository when querying relations. The advantage of eager loading is that generally it solves n+1 problem comparing to you querying a related model on a collection of items. For example, imagine you want to load courses with their respective course modules:

Lazy loading vs eager loading

To avoid the issue described above you should eager load your relation. To eager load a relation you need to use with.

Lazy loading vs eager loading

It is also possible to specify multiple relations to load if you supply an array of relation names or call with multiple times:

Lazy loading vs eager loading

A lot of methods that make sense allow you to eager load the relations, including: first, first_or_fail, one, get, paginate and load_more.

As an alternative for specifying with on the repository you may define on the entity itself to always eager load certain relations.

Belongs to (has many inverse) - definition

If you define it as the property on the entity itself, it will always be eager loaded on supported methods.

Specifying columns to eager load

Sometimes you don't want to download all the fields on the related entity, and it's possible to specify only some columns to load when eager loading related models using the following notation: 'relation_name:column1,column2,column3,etc':

Lazy loading vs eager loading

Applying conditions on eager loading relationships

Sometimes you want to apply conditions when eager loading related models, for example order them in a specific way or add some extra constraints. It is possible without creating a dedicated method on the entity to load a filtered relation.

Lazy loading vs eager loading

Inserting related entities

It is possible to insert related entities using corresponding relation methods. The advantage of using this method is that it takes care of inserting a key into the related entity automatically. For example you can use a save method.

Inserting related entities

Bulk actions on related entities

It is possible to perform bulk actions on related entities using relation method notation on the entity.

Updating

Lazy loading vs eager loading

Deleting

The delete method on the repository will work out of the box as update method described above.

Lazy loading vs eager loading

Future improvements

Features which might get implemented in the future:

Many-to-many relation (having two tables connected using an intermediate table)
Polymorphic relation
Querying existence/absence of the relationship, e.g. calling where_has(), where_doesnt_have() on the entity repository
Counting related models.
Attach/detach feature for belongs_to relation

Links

Since we designed our ORM based on Laravel's one, the relationships follow the same idea. You can read about Laravel Eloquent ORM relationships here: https://laravel.com/docs/5.8/eloquent-relationships