Using bindModel to get to deep relations

Often times you will need to query a distant table based on a condition on a close table. For example we have a store that has a table structure like the following:

Now we want to find the Division that a particular Item belongs to. In normal SQL we could do

Show Plain Text
  1.  
  2. SELECT Division.id FROM items AS Item
  3. LEFT JOIN categories AS Category ON Category.id = Item.category_id
  4. LEFT JOIN sections AS Section ON Section.id = Category.section_id
  5. LEFT JOIN divisions AS Division ON Division.id = Section.divison_id
  6. WHERE Item.id = $id
  7.  

And everything will happen in one query. In CakePHP we could use Containable to get at these deep relations. With a find call like

Show Plain Text
  1. $this->find('first', array(
  2.     'conditions' => array('Item.id' => $itemId),
  3.     'contain' => array('Category' => array('Section' => array('Division')))
  4. ));

This will work just fine however, it will also do 3 queries, one per table. In order to get the optimal query and stay inside the realm of non query() function we can use some bindModel() trickery.

Show Plain Text
  1. $this->unbindModel(array(
  2.     'belongsTo' => array('Category')
  3. ));
  4.  
  5. $this->bindModel(array(
  6.     'hasOne' => array(
  7.         'Category' => array(
  8.             'foreignKey' => false,
  9.             'conditions' => array('Category.id = Item.category_id')
  10.         ),
  11.         'Section' => array(
  12.             'foreignKey' => false,
  13.             'conditions' => array('Section.id = Category.section_id')
  14.         )
  15.         'Division' => array(
  16.             'foreignKey' => false,
  17.             'conditions' => array('Division.id = Section.division_id')
  18.         )
  19.     )
  20. ));
  21. $result = $this->find('first', array(
  22.     'conditions' => array('Item.id' => $id),
  23.     'contain' => array('Category', 'Section', 'Division'),
  24.     'fields' => array('Division.id')
  25. ));

The above although much longer will create only one query. This will provide the fastest result performance wise.

How does it work?

Using unbindModel() and bindModel() allows us to set up custom model bindings for one query. We then disable the automagic foreignKey connection to the Item model as that would result in invalid SQL. Instead we define a set of conditions for each binding. It is these conditions that create the joins between the tables, and allow everything to be executed as one SQL statement. Combine these custom joins with ContainableBehavior and we can create super efficient queries that query and process exactly the data we need and nothing more.

Comments

good solution!
I’ve “released” the LinkableBehavioron github that works on deep relations right in the queries using the ‘joins’ param, avoiding binding overhead and stuff – it’s very lightweight. It can easily replace Containable as I implemented almost the same syntax. So you can reach the same result in your approach doing:

$result = $this->find('first', array(
    'link' => array('Category' => array('Section' => 'Division')),
    'conditions' => array('Item.id' => $id),
    'contain' => array('Category', 'Section', 'Division'),
    'fields' => array('Division.id')
));

the link is: http://github.com/rafaelbandeira3/cakephp-plugins/tree/master/behaviors/linkable.php

anonymous user on 14/11/08

Oh, forgive my dumbness… I forgot to take off the “contain” key on the array…

$result = $this->find(‘first’, array(
    ‘link’ => array(‘Category’ => array(‘Section’ => ‘Division’)),
    ‘conditions’ => array(‘Item.id’ => $id),
    ‘fields’ => array(‘Division.id’)
));

anyway… the code has usage examples and regards about use cases.
cheers

anonymous user on 14/11/08

I’m a little puzzled as to why anyone would want to write all that code, when the same thing can be achieved using a 3 line raw SQL query. Is the model::query method really all that bad?

anonymous user on 14/11/08

i’ve been factoring out bindModels on the basis that the code is cleaner (smaller) and had assumed the performance was largely the same. perhaps I was wrong, should have looked harder.

I also seem to recall I had problems previously with testing models and binds so I fell back to contain/query etc, which has worked well.

@Joel… somewhat agree. however i continue to use query on the assumption that eventually the functionality / performance will catch up with the convenience, it does take periodic leaps. also the earlier point about testing / different DB’s holds, occasionally can get probs with some “hardcoded” queries if not done right.

maybe i’m getting lazy too, cakes find looks nicer in src code than query, and i have to look at it a LOT :-)

anonymous user on 14/11/08

If you use find(‘all’), there is no need for the ‘contain’ key, as it will grab all the related models based on the hasOne bind (therefore building the right JOIN).

@Joel
When you use query() use lose the callbacks such as afterFind(), you need to manually escape your fields and properly build as SQL to ensure that fields returned as they would be by find() and, of course, you may have to adjust your SQL if you ever switch data sources. So extra line or two of code isn’t a big and a better approach… within the framework context.

anonymous user on 14/11/08

@Joel it really depends on what you’re building. A website will, pobrably, never switch from it’s basic php+mysql host. Now when building more complex corporative applications and comercial softwares, you have to have in mind that not every client relies on MySql, and others already have their own server with it’s all mighty and filled with data Oracle. Will you then just rebuild a production ready sw to fit some clients specification? No, you invest a little more time and build everything the most flexible as it can be abusing of arrays, custom and builtin params and methods to abstract the db layer.

@Mark Story when “website” is not supplied with “http://” it is simply ignored, and now links on my name are redirecting to your site with mine’s name appended ;-)

anonymous user on 14/11/08

rafaelbandeira3: True, I find many places don’t auto append the http:// for you. But when I get a chance I’ll add a check for that in. I saw the LinkableBehavior as well, its definitely would reduce the coding in the example I gave.

Joel: Portability is the primary issue, like rafaelbandeira said. If you ever need to migrate database servers and you have a pile of SQL in your code you are pooched. The other is doing UnitTests. Your testing becomes very tricky when you have to have the exact table names in your testing environment.

teknoid: good to know :)

mark story on 15/11/08

Yup, really usefull feature that I used several months.

But be carefully, if you will set ‘dependent’ to true for such db, you can lost all data from many tables.

anonymous user on 15/11/08

Yup, really usefull feature that I used several months.

But be carefully, if you will set ‘dependent’ to true for such db, you can lost all data from many tables.

anonymous user on 15/11/08

Joel Moss, because it is allow to use paginate. Plain sql is not so pretty.

anonymous user on 15/11/08

I could agree only with @skiedr about pagination, because Cake make it easy, but in my 10 years experience in programming and I never changed my database, never. This is not excuse, but I think that this is common case.

Offtopic: I really like CakePHP, but it’s not created for blog platform – all common field names are so different and I need to type them again (author, email, web) :)

anonymous user on 26/11/08

Excellent! Really useful feature. Thanks a lot.

@teknoid:
Cake produces many queries in case of ‘Model->hasMany->hasOne’. It generates multiple queries to the third model. I hope that Mark’s tip will work better in this case.

anonymous user on 6/12/08

I don’t think that CakePHP is not intended for blog platform in the least. I would say it accomplishes this goal quite easily. The author of an app has full control over the fieldnames as well. I just chose to name mine the default. As for having to type things in again, I’m planning to implement a cookie to remember a persons information. Just haven’t had the time.

mark story on 7/12/08

Hi All,

I tried this solution using cakephp 1.2 RC2 and It doesn’t work on me.

Here is my code.

$this->Course->bindModel( array( ‘hasMany’=> array( ‘CourseSchedule’ => array( ‘conditions’ => array(‘CourseSchedule.start_date <= ‘ . date(‘Y-m-d’)) ) ) ) ); $results = $this->Course->find(‘list’, array( ‘link’ => array(‘CourseSchedule’), ‘conditions’ => array(‘Course.course_type_id’ => $course_id) ) );

What seems to be wrong with my code.

I hope you could help me.

thanks in advance.
rifeman2007

anonymous user on 9/12/08

rifeman2007: Looks like you have a mix of my approach and rafaelbandeira3’s Linkable behavior. Perhaps that is the issue.

mark story on 10/12/08

What if would be ‘Item has many Category’ and I would like to get exact item with its categories, sections and so on.
Is it possible do that in one query?

Raph on 18/12/08

Mark, Certainly very useful post. It would be excellent if you could post it with model. I am new and I understand that can be written in to model but confused how to user after binding that.

Chirayu on 4/3/09

Hi!

This is really great!

My question is:

Is this possible with HABTM associations? Has you a post explaining it?

Thanks a lot.

Antonio Marco on 12/3/09

Antonio Marco: You can do habtm as well, you just treat the join table like all the other tables as described, so it takes 2 binds. One to get onto the join, and one to get to the other table.

Chirayu: I don’t have a model I can paste up, but if you put the bindings into a model method. For example $this->Item->bindToDivision() That method would contain all the bind()/unbindModel() calls. You could then call $this->Item->find() and use the bindings you just set.

Raph: This type of query is more intended for getting things off of one table that is far away. If you want all the related data, you are probably better off just using ContainableBehavior

mark story on 12/3/09

< prev12

Have your say:

*
* You can use Textile markup, but be reasonable