Linkable Behavior. Taking it easy in your DB

LinkableBehavior is the implementation to solve ContainableBehavior’s inextensibility, complexity, “featurity” and – mainly – it’s db usage.
Working on db normalization on one of my ongoing projects, I found myself killing app’s performance when trying to push deep related data from tables. Some simple functionality had a processing time addition of more than 5 seconds while Containable was doing it’s dirty job assembling my wanted dataset with 300+ db hits. 300+ for datasets that could be easily fetched with few joins in a single SQL query, using not more than some miliseconds on a production-all-filled-with-data database.
The solution implemented to fetch deep relation data, with no performance hit on app side and no complex model binding hack, is to work right into the query data passed to Model::find, ordering sql JOINs generation using the ‘joins’ for the query data. That’s why the class will only act on beforeFind callback looking for the ‘link’ param to map relations and add needed joins. Usage is simple:

$Schedule->find('all', array(
'link' => array('Task' => array('Project' => 'Company')),
'fields' => array('Schedule.check_in', 'Task.title', 'Project.title', 'Company.cod'),
'conditions' => array('Company.name' => '3Solutions')
));

you can use ‘field’ param inside links specification

$Schedule->find('all', array(
'link' => array('Task' => array(
'fields' => array('title', 'progress', 'priority', 'severity'),
'Project' => array(
'fields' => array('title', 'created', 'status'),
'Company' => array(
'fields' => array('trade_name', 'cod')
)
)
),
'fields' => array('Schedule.check_in'),
'conditions' => array('Company.name' => '3Solutions')
));

and you can also define joins specifications

$db =& $this->getDataSource();
$isAssigned = $db->expression(sprintf(
'(SUM(IF(project_id = %s, 1, 0)) > 1) as %s',
$db->value($project_id),
$db->name('is_assigned')
));
$this->find('assignments', array(
'link' => array('User' => array('Person', 'type' => 'RIGHT')),
'fields' => array($isAssigned, 'User.id', 'Person.name'),
'conditions' => array($this->escapeField('project_id') => 1),
'group' => $this->escapeField('user_id')
));

Notice that relation mapping is implemented in Containable’s fashion, with enclosing arrays making a kind of path, so if you want to fetch or query data about the Company related to a scheduled task, you have to specify where this relation comes from, by enclosing relations by reference, in this particular example, the ‘link’ param could be supplied like this:

$Schedule->find('all', array(
'link' => array('Task' => array('Project' => 'Company'))
));

Every relation type is supported, but notice that hasMany and hasAndBelongsToMany, that can potentially associate more than one record to the main – right most – table record, won’t reproduce ContainableBehavior’s behavior, fetching all related data and binding it to the resultset following Cake’s data format. When fetching data use the right most model to perform the find operation:

$Tag->find('all', array(
'link' => 'Post',
'conditions' => array('Post.title LIKE' => 'the pipettes rock')
));

Notice that returned data will be just like directly related – recursive 0 – data. Only one record per model, all models in the same dimension:

[0] => Array(
[Schedule] => [...],
[Task] => [...],
[Project] => [...],
[Company] => [...]
),
[1] => Array(
[Schedule] => [...],
[Task] => [...],
[Project] => [...],
[Company] => [...]
)

Further more, notice that only models with fields being fetched will appear on the resultset, which means that even if you link a model it won’t necessarily appear on the resultset.
Try yourself. Change a ‘contain’ key for ‘link’ and make friends with your db again.
PS. Don’t you forget to catch on my typos!!!
Tagged: Behavior, ContainableBehavior, Data Handling, LinkableBehavior, Model, Open Source