You are Here: Articles » Optimising Queries with ContainableBehavior

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. 

  2.                 'Deal' => array(
    3. 

  3.                         'fields' => array(
    4. 

  4.                                 'id', 
    5. 

  5.                                 'title', 
    6. 

  6.                                 'created'
    7. 

  7.                         ), 
    8. 

  8.                         'order' => 'Deal.created DESC',
    9. 

  9.                         'limit' => 6,
    10. 

  10.                         'User.username'
    11. 

  11.                 ),
    12. 

  12.                 'Image.id',
    13. 

  13.                 'Tag', 
    14. 

  14.                 'User.username'
    15. 

  15.         )
    16. 

  16. );
    17. 

  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. 

  2. (

    3. 

  3.     [Thing] => Array

    4. 

  4.         (

    5. 

  5.             [id] => 1

    6. 

  6.             [name] => Microsoft PowerPoint

    7. 

  7.             [slug] => microsoft_powerpoint

    8. 

  8.             [description] => Slideshow generation software.

    9. 

  9.             [user_id] => 1

    10. 

  10.             [is_visible] => 1

    11. 

  11.             [created] => 2008-05-12 23:01:40

    12. 

  12.             [modified] => 2008-05-12 23:01:40

    13. 

  13.         )

    14. 

  14.  
    15. 

  15.     [User] => Array

    16. 

  16.         (

    17. 

  17.             [username] => paulherron

    18. 

  18.         )

    19. 

  19.  
    20. 

  20.     [Deal] => Array

    21. 

  21.         (

    22. 

  22.             [0] => Array

    23. 

  23.                 (

    24. 

  24.                     [id] => 1

    25. 

  25.                     [title] => Buy PowerPoint Cheap!

    26. 

  26.                     [created] => 2008-05-13 23:01:40

    27. 

  27.                     [user_id] => 1

    28. 

  28.                     [thing_id] => 1

    29. 

  29.                     [User] => Array

    30. 

  30.                         (

    31. 

  31.                             [username] => paulherron

    32. 

  32.                         )

    33. 

  33.  
    34. 

  34.                 )

    35. 

  35.  
    36. 

  36.         )

    37. 

  37.  
    38. 

  38.     [Image] => Array

    39. 

  39.         (

    40. 

  40.             [0] => Array

    41. 

  41.                 (

    42. 

  42.                     [id] => 1

    43. 

  43.                     [thing_id] => 1

    44. 

  44.                 )

    45. 

  45.  
    46. 

  46.             [1] => Array

    47. 

  47.                 (

    48. 

  48.                     [id] => 2

    49. 

  49.                     [thing_id] => 1

    50. 

  50.                 )

    51. 

  51.  
    52. 

  52.             [2] => Array

    53. 

  53.                 (

    54. 

  54.                     [id] => 3

    55. 

  55.                     [thing_id] => 1

    56. 

  56.                 )

    57. 

  57.  
    58. 

  58.         )

    59. 

  59.  
    60. 

  60.     [Tag] => Array

    61. 

  61.         (

    62. 

  62.             [0] => Array

    63. 

  63.                 (

    64. 

  64.                     [id] => 1

    65. 

  65.                     [tag] => Slideshow software

    66. 

  66.                     [slug] => slideshow_software

    67. 

  67.                     [is_visible] => 1

    68. 

  68.                     [created] => 2008-05-12 23:01:40

    69. 

  69.                     [modified] => 2008-05-12 23:01:40

    70. 

  70.                     [TagsThing] => Array

    71. 

  71.                         (

    72. 

  72.                             [id] => 1

    73. 

  73.                             [tag_id] => 1

    74. 

  74.                             [thing_id] => 1

    75. 

  75.                             [user_id] => 1

    76. 

  76.                             [is_visible] => 1

    77. 

  77.                             [created] => 2008-05-13 23:01:40

    78. 

  78.                             [modified] => 2008-05-13 23:01:40

    79. 

  79.                         )

    80. 

  80.  
    81. 

  81.                 )

    82. 

  82.  
    83. 

  83.         )

    84. 

  84.  
    85. 

  85. )

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

Comments

Katherine wrote on 26/8/11:

Great aritcle, thank you again for writing.

cmdnydtaoi wrote on 26/8/11:

DyLt9l huaczvkecfli

hottdanca wrote on 30/8/11:

valtrex buy it cheap %P apothecure and colchicine and 779

sellenchops wrote on 4/9/11:

colchicine :-[ ultram and lexapro 04980

christian louboutin outlet wrote on 1/11/11:

Your blog post is without a doubt brilliant; you should not considerably ofthings persons are preaching about learning ability. My organization is thrilled Concerning this opportunity throughout my relatingsome belonging to the investigation..I really like the application. have some article writing and submission software programs for your types of fabulous piece of writing in regards to this posting.Appreciate your sharing people giving.

office2010key wrote on 16/11/11:

The Microsoft office 2011 users can Office 2010 work more efficiently with the Microsoft Office 2010 simple and intuitive Download Office 2010 user interface new graphical MS Office 2010 menus and a familiar Office 2010 Key experience provide the Office 2010 Download tools you need to plan and Office 2010 Professional manage your project. And the Microsoft Office 2010 Download tabs and contextual menu buy microsoft office 2010 groupings reveal powerful features, Windows 7 making it easier to get Microsoft Windows 7 started and realize results Win 7. Simply be more productive Download Windows 7 by choosing your own menu Buy Windows 7 options, quick access Office 2007 toolbar settings and Microsoft Office 2007 setting defaults.Enhanced copy Office 2007 Download and paste makes it easy to Office 2007 Professional share information effectively MS Office 2007 with other Office 2007 Key applications while retaining buy microsoft office 2007 formatting. And the added columns Download Office 2007 dynamically by entering Microsoft Office 2007 Professional data without having to first select purchase microsoft office 2007 the data type.

http://softwarestockss.blogspot.com

http://software-stocks.com

Lorraine wrote on 24/12/11:

Hey, keillr job on that one you guys!

hilogbzkpgk wrote on 26/12/11:

gmV9fh bdexsohumuar

penny wrote on 27/12/11:

Nevertheless ghd gold classic styler supplies the strongest conditions in high-quality it is probably not going to help you rest readily, ghd pink orchid limited edition bring one-year service contract. The application comprises every that belongs in the components you have got often had to include for just a ugly locks appearance method GHD Glamour, about the same several other offer distinct from a single thing manufactured at present, The actual ghd mini styler provides in order to lead 10 lbs of each and every buy in order to Breasts Most cancers Research. In addition to could it be the life span background for that supply may be the location After i connect to my own web site personal pink ghd check out greatest costs online intended for competent merchants associated with great times Internet.To be certain you're receiving a genuine ghd midnight collection, read the recognized ghd web site in order to research for any certified ghd store.Should your GHD Purple Indulgence get quit doing work as well as get come to be wrong, remember to tend not to worry even as we can just restoration your current GHD New Wave.These kind of astounding ceramic flat irons get removed on get over everyone and they are applied through the globe, similar to by simply distinguished stars.

4234 wrote on 2/1/12:

Today's start off to really feel slightly bit like Groundhog Day, louis vuitton sale is not it? A few hours ago, we brought to you a story about Louis Vuitton suing a media organization for violating its trademarks, and now we have news of a provider that thinks it is a very good concept to print the visage of an Hermes Birkin on a cheap louis vuitton canvas bag: the V73 Canvas Printed Tote. The day's handbag news is downright retro. you probably could have guessed how this campaign would search louis vuitton speedy sale pale, valuable and ever so sweet. The louis vuitton sale brand tapped Steven Meisel to shoot Daria Strokous and Kati Nescher in the world's most luxurious ice cream shop, and the outcome match the mood in the louis vuitton speedy 25 clothes and accessories perfectly. Check out another shot right after the jump.the Kelly form louis vuitton neverfull MM a straightforward, arm-carried bag having a front flap closure and a major deal withlouis vuitton neverfull is often a traditional form that will not definitely belong to louis vuitton speedy 30 a lot as it belongs to handbag layout in general. Significantly like the louis vuitton luggage, the shape has reached this kind of a classic degree that attributing it to only a single business no longer seems fair. And when the reinterpretations yield bags as unique in the original since thelouis vuitton speedy Postman's Lock Satchel, I believe it is simple to see why I take that opinion 

hong wrote on 3/1/12:

bvcbcvbb

<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">freshwater pearl earrings</a></p>
<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">white pearl earrings</a></p>
<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">pearl dangle earrings</a></p>
<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">clip on pearl earrings</a></p>
<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">cultured pearl earrings</a></p>
<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">pink pearl earrings</a></p>
<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">chandelier pearl earrings</a></p>
<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">pink pearl earring</a></p>
<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">cluster pearl earrings</a></p>
<p><a href="http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html">pearl hoop earrings</a></p>
<p><a href="http://www.lpearls.com">buy pearl earrings</a></p>

 

hong wrote on 3/1/12:

gdfgdfgdfg

[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]freshwater pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]white pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]pearl dangle earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]clip on pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]cultured pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]pink pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]chandelier pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]pink pearl earring[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]cluster pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.h

hong wrote on 3/1/12:

fdgfhfghgmjhk

[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]freshwater pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]white pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]pearl dangle earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]clip on pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]cultured pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]pink pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]chandelier pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]pink pearl earring[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.html]cluster pearl earrings[/url]
[url=http://www.lpearls.com/Pearl-earrings-&-pendants/c120/index.h

ChaquetasBelstaff wrote on 3/1/12:

Belstaff Bolsa Los datos más recientes mostraron China foreign exchange Trade Center, el 29 de noviembre, la tasa de paridad central de 6.3587 Yuan frente al dólar, una devaluación Belstaff Chaquetas Hombres del anterior período de sesiones 2 puntos de base, comparado Belstaff chaquetas Mujeres con el 31 de octubre tasa de paridad central de 6.3233 renminbi?os en el futuro el Yuan cayó frente al dólar, mostrar la devaluación del renminbi en las expectativas del mercado hacia adelante.Belstaff Chaquetas icon 　　"Depreciación MBT Zapatos actual renminbi debido principalmente a China en octubre representaron un crecimiento negativo de refugio deberá iniciar cierto nivel salida de capital a corto plazo. "Banco de comunicaciones, centro de investigación financiera investigador e Belstaff chaquetas Mujeres, pero los márgenes de ganancia en el mercado inmobiliario se más comprimirán, bajo la influencia de la turbulencia en los mercados financierosBelstaff Chaquetasinternacionales, en el mercado de valores de China masivos,. que a su vez afecta la tasa de cambio del Renminbi MBT Outlet

Leave a Comment

CAPTCHA[Refresh]

« Articles

Article Tags

Show all articles, or just those tagged as:

Feed

The articles RSS feed is available.

Elsewhom

See More…

Back to top.