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):

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.

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

$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:

$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 1-on-1 relationship to the other entity

/**

 * @return has_one

 */

public function client_settings(): has_one {

    return $this->has_one(client_settings::class, 'client_id');

}

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

/*

...

 @property-read client_settings $client_settings

 */

class client extends entity {

...

client_settings Entity class (related entity)

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

/**

 * @return belongs_to

 */

public function client(): belongs_to {

    return $this->belongs_to(client::class, 'id');

}

client Model class (main model)

Add an annotation above the model class for the related model

* @property-read client_settings_model $client_settings

 */

class client extends model {

...

Add the related model to the accessor whitelist

protected $model_accessor_whitelist = [

    'tenant_entity',

    ...

    'client_settings'

];

For the ORM, you will need to add a method named like "get_[accessor property]"

public function get_client_settings(): client_settings_model {

    return client_settings_model::load_by_entity($this->entity->client_settings);

}

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)

$client_settings_entity  = new client_settings();

$client_settings_entity->time_created = time();

// Add a 1-on-1 relationship from client to client_settings

$entity->client_settings()->save($client_settings_entity);

Test

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

$name = 'test';

$description = 'description_test';

$model_client = client_model::create($name, $description); // the main model will automatically save a related entity (see above)

// Get the related model

$client_settings_model_related = $model_client->client_settings;

$this->assertEquals($model_client->id, $client_settings_model_related->client_id);

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:

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.

$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:

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.
    }
}

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).

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:

$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.

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).

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:

$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.

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.

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'); 
    }
}

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:

$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();

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:

// ...
$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.

// 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:

// 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.

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

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':

// ...
$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,
]
*/

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.

// ...
$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.

// ...
$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.

Bulk actions on related entities

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

Updating

// ...
$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.

// ...
$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

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