There are two approaches I use for multi-table operation, depending on what I want to achieve.
1) Database Views or JOIN statements: for read-only operation to get flat data (rows) with a single select
For instance, to get a list of films that have been given an academy award with all the details from both Film and Awards tables.
In this case you do not need afterFind(), because you get all the data in a single SELECT statement.
2) Higher Order Entity + one base model and entity per DB table: to read AND write hierarchical data
For instance, to get a complete record of a movie with the directors, actors, awards, links to trailers, reviews etc.
I guess this is where you could use afterFind() method in your MovieModel so that when the movie is found, you can take care of loading all the subordinate data like actors in your afterFind() method:
Code:
class MovieModel extends Model {
(...)
protected function afterFind(array $data)
{
$data['data']['actors'] = $this->actorModel->where('movie_id', $data['id'])->findAll();
$data['data']['awards'] = $this->awardModel->where('movie_id', $data['id'])->findAll();
(...)
return $data;
}
}
My personal opinion on handling structured data in a flat database like MySQL is this. Since Entities and Models are - in my opinion - meant to work on flat data (single table row) and a single database table (or view, that is), extending them to work well with hierarchical data would result in a mess.
So I decided to create a totally independent set of classes to handle the situation. This I call DataStructure and HigherOrderDataStructure. Basically, a DataStructure holds an entity and a model for a single database table, while HigherOrderDataStructure holds the 'top level' entity and model – like the Movie in our example – and an arbitrary number of 'subordinate' DataStructures like Actor. Those supordinates can also be HigherOrderDataStructures – like Actors can have multiple Awards – to achieve multiple levels of hierarchy like in an XML or JSON file. Since HigherOrderDataStructure is designed from de ground up to work with subordinates, it can inherently handle the afterFind() situation itself. Also I can hide the entity and model classes from the rest of the application. I can think of DataStructure as an adapter to CI4's Entities and Models – in my DataStructure I could work around a CI4 bug in Model->save() method resulting in exception when I try to save an unchanged entity into database table.
