Linkable Behavior. Taking it easy in your DB

November 16, 2008 · 25 Comments

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!!!

Categories: CakePhp · Code Release
Tagged: Behavior, ContainableBehavior, Data Handling, LinkableBehavior, Model, Open Source

25 responses so far ↓

Signets remarquables du 14/11/2008 au 17/11/2008 | Cherry on the... // November 17, 2008 at 4:03 pm | Reply

[...] Linkable Behavior. Taking it easy in your DB [...]
Jonah Turnquist // November 22, 2008 at 2:16 am | Reply

I’m not sure I’m understanding how this is different from containable. I believe containable will already optimize the quarries to use JOINS if I am correct?
rafaelbandeira3 // November 22, 2008 at 12:24 pm | Reply

@Jonah: Thanks for your comment.
No containable haven’t been optimized yet, but that’s not the only difference. LinkableBehavior won’t produce the same result of ContainableBehavior on 1:n and n:n relations, ever. Containable will always need to perform additional queries to achieve it’s goal in those cases, while Linkable will always produce one query, and therefore will only be able to return results as such – wich goes on “all models in the same array dimension” and “only one model entry per entry”. Going further, Linkable will allow you to filter domain model’s data on deep relationed tables and build resultsets based on it for different find types.

Please, let me know if it’s not clear yet, if a follow-up post is needed, than I would really apreciate work on it.
Nachopitt // December 1, 2008 at 8:16 pm | Reply

Hey rafael, I’ve been using this Behavior since like 5 days ago… I have to express to you my immense joy for finding this working incredible nice.

I have just a problem, its with the HABTM associations, involving Suaje HABTM Mqna (tables are named ‘tbsuajes’ and ‘tbmqnas’, and the joinTable is named ‘tbmqnas_suajes’) I have also defined a 3rd class for the joinTable name MqnasSuaje, belonging to Mqna and Suaje. I have also defined the prefix ‘tb’ for all the tables in my db.

The resulting table in the query for the join model is ‘mqnas_suajes’ and not ‘tbmqnas_suajes’, and Mysql complaining that the table ‘mqnas_suajes” doesn’t exists.

Does this require a Patch, a test case or something? How can I help solving this? Thanks in advance.
rafaelbandeira3 // December 2, 2008 at 2:42 am | Reply

@Nachopitt ripped off this one too. Thanks for pointing it out!

I am trying really hard to write Linkable a test case, just didn’t got the time and patience for it – I have a super complete test case for it, but it’s all customized with my project’s fixtures and code…
If someone is interested in helping me out: just fork it!

Regards
Nachopitt // December 3, 2008 at 6:57 pm | Reply

Thank you so much, rafael. About the “on the fly” associations, your test cases pointed me out that I was using Model::bindModel() instead Model::bind()… actually I dont know the difference between those 2, the descriptions are the same… but in the inners ‘bind’ seems more complex.
Nachopitt // December 3, 2008 at 7:01 pm | Reply

Ooops, wrong post, Sorry for my mistake.
rafaelbandeira3 // December 3, 2008 at 7:08 pm | Reply

@Nachopitt no problem, and you’re welcome.
Basic differences between Model::bindModel and Model::bind are the usage and, most important, that, *by default*, Model::bindModel bindings will least one query only, while Model::bind bindings will be permanent.
Phally // December 19, 2008 at 2:08 pm | Reply

The link in the beginning of the post is dead.
Deon // January 8, 2009 at 4:39 pm | Reply

Great article, however I’m having difficulties doing the following.

Event hasMany Occurrence
Venue hasMany Occurrence
Complex hasMany Venue
City hasMany Regions

I need to link Event where Region.id = 1

Is there a way to achieve the results using this behavior ? I have tried numerous ways to link but to no avail.

Thanks
Deon
rafaelbandeira3 // January 9, 2009 at 3:46 pm | Reply

@Phally Thanks, fixed.

@Deon Of course there’s a way to achieve this using LinkableBehavior, you just need to know the relation path between those models. I can’t see where would Event be associated with Region in the mentioned scheme.
Jacques // January 12, 2009 at 1:12 pm | Reply

How would one set the behavior to linkable…I would assume one would just do it as:
$actsAs = array (’Linkable’);
in the model ?

Correct me if I’m wrong pls.

Thanks.

Regards,
Jacques.

rafaelbandeira3 // January 12, 2009 at 10:26 pm | Reply

@Jacques hey Jacques, if you have just cloned it from the “linkable” repo on github, than you should use : ‘Linkable.Linkable’ to refer to the behavior inside the plugin.

Deon // January 15, 2009 at 6:46 am | Reply

Rafael, is it possible to paginate using the linkable behaviour
CakePHP : signets remarquables du 20/01/2009 au 22/01/2009 | Cherry on the... // January 21, 2009 at 11:02 pm | Reply

[...] Nate Abele descend dans l'arène pour nous proposer une sympathique extension à Model::find() qui permet de faire une recherche sur habtm (cas d'école : les tags). A noter que Rafael Bandeira avait également proposé une élégante contribution déjà relevée ici sur le sujet avec le Linkable Behavior. [...]
links for 2009-01-30 « Richard@Home // January 31, 2009 at 5:00 am | Reply

[...] Linkable Behavior. Taking it easy in your DB « Something on Everything This should come in handy! (tags: cakephp deep query performance behavior linkable) [...]
drshit // February 1, 2009 at 11:55 pm | Reply

Yes – please can you post an example of how you would use linkable with pagination, it would really help understand how to use your syntax.
drshit // February 2, 2009 at 12:35 am | Reply

can you pull data from 2 HABTM associations? e.g. if A HABTM B and A HABTM C, can you pull data from both B and C? when I try I get ‘Warning (2): array_merge() [function.array-merge]: Argument #1 is not an array’.
Maybe it’s just not possible?
Damon // February 3, 2009 at 10:25 pm | Reply

Nice. I added the following to line 136 to be lazy about fields:

if (empty($query['fields'])) {
$query['fields'] = array_keys($Model->_schema);
}
Kjell // March 1, 2009 at 3:34 pm | Reply

If you reference User two times (using two foreignKeys like user_id, owner_id) the foreignKey defined in reference model is not respected.

Instead, user_id is used because of “class” being “User”. Usually it should use whatever foreignKey is defined in the aliased Model.

example:
tasks (user_id, owner_id)

Task belongsTo
Project (foreignKey=’project_id’)
User (foreignKey=’user_id’, class=’User’)
Owner (foreignKey=’owner_id’, class=’User’)

Task->find(… link(User, Owner, Project))

With a setup similar to this Owner is joined ON Owner.id = Task.user_id

rafaelbandeira3 // March 3, 2009 at 11:33 pm | Reply

hey mentor, how are you!?
Great observation! You know I got this fixed somewhere in my personal machine. Once I got the time to take a look at it again, I’ll make sure to post a patch, in the mean time, please feel free to contribute!
thanks for your comment, and keep up the good work!

wilku // March 3, 2009 at 6:36 pm | Reply

Not working with some $paginator->sort().
I don’t know why.

rafaelbandeira3 // March 3, 2009 at 11:35 pm | Reply

hi wilku, right now I got no time to test it, but I’m pretty sure it might have nothing to do with the behavior itself, maybe it’s the way the parameters are disposed or something like that, please take a deeper look and feel free to post your code here – actually, post it on bin and comment it here. Thanks!

Mijel // April 2, 2009 at 3:21 pm | Reply

Linkable Behaviour saved my day! I’m a cakePHP believer again.
Kudos to Mr Bandeira. Muito obrigado!
Legion // July 3, 2009 at 2:48 pm | Reply

Thank you very much!!!
It worked like a charm, just download, add
var $actAs = array(’Linkable’); to my model,
and the line ‘link’ => array(’Archivo’) to the paginate array and……. magic!!

Something on Everything