You are Here: Articles » Optimising Queries with ContainableBehavior

You are not authorized to access that location.

Optimising Queries with ContainableBehavior

A new addition to the Cake core, Containable is a powerful behavior for declaring what data should be returned by model finds.

Tagged with WAMP, CakePHP and Web Development
Posted on 1/6/08 by Paul Herron

One of the pleasures of developing with nightly builds of CakePHP is seeing new functionality constantly finding its way into the core. Changeset 6918 saw the addition of ContainableBehavior, which allows for some really powerful query optimisation.

In the past, associated model data would usually be included or excluded using $this->recursive statements, bindModel() and unbindModel(). This would often do the trick, but these declarations were often verbose and didn't give much flexibility in setting where recursion should occur. Without some serious fiddling, it would often be a case of retrieving too little associated model data, or way too much.

Containable allows you to state in the controller exactly what to retrieve. You can declare which fields are needed from which associated models, all with a flexible and intuitive syntax. This is probably best illustrated with an example:

  1. $this->Thing->contain(array(
  2.                 'Deal' => array(
  3.                         'fields' => array(
  4.                                 'id',
  5.                                 'title',
  6.                                 'created'
  7.                         ),
  8.                         'order' => 'Deal.created DESC',
  9.                         'limit' => 6,
  10.                         'User.username'
  11.                 ),
  12.                 'Image.id',
  13.                 'Tag',
  14.                 'User.username'
  15.         )
  16. );
  17. $thing = $this->Thing->findBySlug($slug);

That query is based on the idea that Thing hasMany Deal, Thing hasMany Image, Thing hasAndBelongsToMany Tag and Thing belongsTo User.

Expressed in English, that's asking Cake to query the Thing model for an entry with a slug of $slug, and also to include:

  • Up to 6 associated entries from the Deal model. Only the id, title and created fields should be included, and these records should be ordered from newest to oldest. For each Deal, the username of the associated User should also be fetched.
  • All associated images, but only the id field for each one.
  • All associated tags.
  • The associated User, but only the username field.

This would return the following array:

  1. Array
  2. (
  3.     [Thing] => Array
  4.         (
  5.             [id] => 1
  6.             [name] => Microsoft PowerPoint
  7.             [slug] => microsoft_powerpoint
  8.             [description] => Slideshow generation software.
  9.             [user_id] => 1
  10.             [is_visible] => 1
  11.             [created] => 2008-05-12 23:01:40
  12.             [modified] => 2008-05-12 23:01:40
  13.         )
  14.  
  15.     [User] => Array
  16.         (
  17.             [username] => paulherron
  18.         )
  19.  
  20.     [Deal] => Array
  21.         (
  22.             [0] => Array
  23.                 (
  24.                     [id] => 1
  25.                     [title] => Buy PowerPoint Cheap!
  26.                     [created] => 2008-05-13 23:01:40
  27.                     [user_id] => 1
  28.                     [thing_id] => 1
  29.                     [User] => Array
  30.                         (
  31.                             [username] => paulherron
  32.                         )
  33.  
  34.                 )
  35.  
  36.         )
  37.  
  38.     [Image] => Array
  39.         (
  40.             [0] => Array
  41.                 (
  42.                     [id] => 1
  43.                     [thing_id] => 1
  44.                 )
  45.  
  46.             [1] => Array
  47.                 (
  48.                     [id] => 2
  49.                     [thing_id] => 1
  50.                 )
  51.  
  52.             [2] => Array
  53.                 (
  54.                     [id] => 3
  55.                     [thing_id] => 1
  56.                 )
  57.  
  58.         )
  59.  
  60.     [Tag] => Array
  61.         (
  62.             [0] => Array
  63.                 (
  64.                     [id] => 1
  65.                     [tag] => Slideshow software
  66.                     [slug] => slideshow_software
  67.                     [is_visible] => 1
  68.                     [created] => 2008-05-12 23:01:40
  69.                     [modified] => 2008-05-12 23:01:40
  70.                     [TagsThing] => Array
  71.                         (
  72.                             [id] => 1
  73.                             [tag_id] => 1
  74.                             [thing_id] => 1
  75.                             [user_id] => 1
  76.                             [is_visible] => 1
  77.                             [created] => 2008-05-13 23:01:40
  78.                             [modified] => 2008-05-13 23:01:40
  79.                         )
  80.  
  81.                 )
  82.  
  83.         )
  84.  
  85. )

This is a simplified example, but hopefully shows how useful Containable can be.

Leave a Comment

CAPTCHA[Refresh]

« Articles

Article Tags

Show all articles, or just those tagged as:

Feed

The articles RSS feed is available.

Elsewhom

  • Hackszine.com.
    Hack the way you think
  • Create Digital Motion.
    Motion graphics, live visuals, VJing, video production, and interactive art
  • Mi Blog.
    CakePHP-related articles, downloads and demonstrations
  • cakebaker.
    Baking cakes with CakePHP
    Enlightening CakePHP articles
  • Coding My Thoughts.
    Java, PHP, and some other technological mumble jumble. Also, some real-life stuff as well.

See More…

Back to top.