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; 

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
class course extends entity {

    public const TABLE = 'course';

    /**
     * Course modules
     *
     * @return has_many
     */
    public function modules(): has_many {
		// This method accepts another argument - key, which defaults to id and will be true in most cases.
        return $this->has_many(course_module::class, 'course'); 
    }
}

class course_module extends entity {
    public const TABLE = 'course_modules';
}

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
$course = course::repository()
			->order_by('id')
			->first();

// This will load and return all the related course modules
$modules = $course->modules()->get(); 
// Or using the property access
$modules = $course->modules;
// SQL: SELECT * FROM {course_modules} "course_modules" where "course_modules".course = ?, [$course->id]

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

Has_many - extra definition constraints
class course extends entity {

    public const TABLE = 'course';

    /**
     * Course
     *
     * @return has_many
     */
    public function active_modules(): has_many {
        return $this->has_many(course_module::class, 'course')
					->where('active', 1); // In addition to key filtering, where condition will be added as well.
    }
}

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
class competency extends entity {

    public const TABLE = 'comp';

    /**
     * Scale values for this competency
     *
     * @return has_many_through
     */
    public function values(): has_many_through {
		// the arguments follow the path from the current one,
 		// through an intermediate table to the target table.
		// current -> intermediate -> target
        return $this->has_many_through(
            competency_scale_assignment::class,  // the intermediate entity class name
            competency_scale_value::class,       // the target entity class name
            'frameworkid',						 // the foreign key in the current table to the intermediate table
            'frameworkid',						 // the key to link to in the intermediate table
            'scaleid', 							 // the foreign key in the intermediate table to the target table
			'scaleid'							 // the key in the target table
		);
    }
}

class competency_scale_value extends entity {
    public const TABLE = 'comp_scale_values';
}

class competency_scale_assignment extends entity {
    public const TABLE = 'comp_scale_assignments';
}

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

Has_many_through - query
$competency = competency::repository()
			->order_by('id')
			->first();

$competency->values;
// SQL: SELECT * FROM {comp_scale_values} "scale_values" JOIN {comp_scale_assignments} "scale_assignments" ON "scale_values".scaleid = "scale_assignments".scaleid where "scale_assignments".frameworkid = ?, [$competency->frameworkid]

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

Limitations

has_many_through relation implementation has the following limitations:

  • Saving related models using relations isn't supported
  • Querying specific columns at eager-loading is not supported

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
class competency extends entity {

    public const TABLE = 'comp';

    /**
     * Scale for this competency
     *
     * @return has_one_through
     */
    public function scale(): has_one_through {
		// the arguments follow the path from the current one,
 		// through an intermediate table to the target table.
		// current -> intermediate -> target
        return $this->has_one_through(
            competency_scale_assignment::class,  // the intermediate entity class name
            scale::class,       				 // the target entity class name
            'frameworkid',						 // the foreign key in the current table to the intermediate table
            'frameworkid',						 // the key to link to in the intermediate table
            'scaleid', 							 // the foreign key in the intermediate table to the target table
			'id'								 // the key in the target table
		);
    }
}

class competency_scale extends entity {
    public const TABLE = 'comp_scale';
}

class competency_scale_assignment extends entity {
    public const TABLE = 'comp_scale_assignments';
}

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

Has_many - query
$competency = competency::repository()
			->order_by('id')
			->first();

$course->scale; 
// SQL: SELECT * FROM {comp_scale} "scale" JOIN {comp_scale_assignments} "scale_assignments" ON "scale".id = "scale_assignments".scaleid where "scale_assignments".frameworkid = ?, [$competency->frameworkid]

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

Limitations

has_one_through relation implementation has the following limitations:

  • Saving related models using relations isn't supported
  • Querying specific columns at eager-loading is not supported

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
class course extends entity {

    public const TABLE = 'course';

    /**
     * Course modules
     *
     * @return has_many
     */
    public function modules(): has_many {
		// This method accepts another argument - key, which defaults to id and will be true in most cases.
        return $this->has_many(course_module::class, 'course'); 
    }
}

class course_module extends entity {
    public const TABLE = 'course_modules';

	/**
     * A course entity
     *
     *
     * @return belongs_to
     */
	public function a_course(): belongs_to {
        // This is the example where the name of the foreign key will match the most logical relation name, so we call it a_course instead of course.
		// Belongs to takes key as another argument which we omit here as in the majority of the circumstances it will
        return $this->belongs_to(course::class, 'course'); 
    }
}

Note on saving related models

You cannot insert related models for belongs_to relationship, however it's still possible to update them.

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.

Extra conditions

Even though it's possible to add extra conditions on the related model repository, it may not make practical sense, as applying foreign key constraint should already narrow it down to exactly one record.

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:

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

// This is a collection of module entities filtered down on the database level
$modules = $course->modules()
   ->where('name', 'module name')
   ->get();

Interact with a relation method as it's a repository

Also using method relation, you don't have to get a collection at the end, you might use count or recordset for example, as well as specify columns you are selecting.

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.

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

// This is a collection of module entities which is cached.
$modules = $course->modules;

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
// ...
$courses = course::repository()->get();

foreach ($courses as $course) {
    // You want to get each course module here
    foreach ($courses->modules as $cm) {
        // Do something...
    }
}

// Running the above code will result in having an n+1 problem as it would first execute a query to get all the courses and then in the loop it will run a query to fetch each course module.
// SELECT * FROM {course} course;
// SELECT * FROM {course_modules} course_modules WHERE course = ? [$course->id]
// ... repeat it as many times as many courses you have

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
// You specify the relation name you want to loadit is also possible to specify multiple relations to load if you supply an array of relation names
$courses = course::repository()
    ->with('modules')  // multiple: ->with(['relation_a', 'relation_b']) or just  ->with('relation_a')->with('relation_b') 
    ->get();

foreach ($courses as $course) {
    // You want to get each course module here
    foreach($courses->modules as $cm) {
        // Do something...
    }
}

// Running the above code will result in executing two queries only:
// When the collection will be loaded, before the result is returned to the user, an extra query will run.
// SELECT * FROM {course} course;
// SELECT * FROM {course_modules} course_modules WHERE course IN (?,?,?,...) [array_column($courses, 'id')]

// After the query to fetch all the related entities has ran, it will automatically inject the correct entities into the main collection
// So by the time you are iterating over courses your modules will be there already and will not trigger extra database queries.

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
// Array syntax
$courses = course::repository()
    ->with(['relation_a', 'relation_b'])   
    ->get();

// Fluent interface
$courses = course::repository()
    ->with('relation_a')
    ->with('relation_b')
    ->get();

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.

Eager loading on recordsets

Currently eager loading relations on recordsets is not supported, due to the fact that we'd have to know all the ids in advance. Alternatively to save memory, eager loading works fine with results pagination. Related models, however, are not paginated.

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
class course extends entity {

    public const TABLE = 'course';

    /**
     * List of always eager-loaded relations.
     *
     * @var string[]
     */
    protected $with = [
        'modules'
    ];

    /**
     * Course modules
     *
     * @return has_many
     */
    public function modules(): has_many {
        return $this->has_many(course_module::class, 'course');
    }
}

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
// ...
$courses = course::repository()
    ->with('modules:instance,section,idnumber') // We want to get only these columns
    ->get();

foreach ($courses as $course) {
    // You want to get each course module here
    foreach($courses->modules as $cm) {
        var_dump($cm->to_array());   
    }
}

// It will output something like:
/*

[
   'id' => 1,
   'instance' => 22,
   'section' => '...',
   'idnumber' => '...',
   'course' => 2,
]
*/

Specifying columns to load on a relation

If you omit the id column, it will be automatically prepended to the array of the columns to load anyway, as well as foreign key. If you omit foreign key, it will be appended automatically to the list of columns to load, due to internal implementation.

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
// ...
$courses = course::repository()
    ->with(['modules' => function(entity_repository $repository) {
        $repository->where('visibleoncoursepage', true)
                   ->order_by('added', 'desc');
    }])
    ->get();
// ...

// This ^^ will only load related models which have "visibleoncoursepage" flag set to true and the collection will be sorted by added field in a descending order.

// It is also possible to specify both: columns to load as well as extra conditions:
// ...
    ->with(['modules:added,module' => function(entity_repository $repository) {
        $repository->where('visibleoncoursepage', true)
                   ->order_by('added', 'desc');
    }])
// ...

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
// ...
$course = course::repository()->first();

$course->modules()->save(new course_module(
    [
        'module' => 5,
        // ...,
    ]));

// This will automatically insert "course" (foreign key) property into the course_module entity.

Inserting availability

Not all the relation types support inserting related models, for example belongs_to relation does not.

Inserting on collections

Currently inserting related entities for collections is not supported.

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
// ...
$course = course::repository()->first(); // Will retrieve a course with id 1 for example

$course->modules()->update([
   'visible' => false,
]);

// Running the above query will set visible flag to false for all modules with course id 1

Deleting

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

Lazy loading vs eager loading
// ...
$course = course::repository()->first(); // Will retrieve a course with id 1 for example

$course->modules()->delete();

// Running the above query will delete all modules with course id 1

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

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