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:

$this->Thing->contain(array(
                'Deal' => array(
                        'fields' => array(
                                'id', 
                                'title', 
                                'created'
                        ), 
                        'order' => 'Deal.created DESC',
                        'limit' => 6,
                        'User.username'
                ),
                'Image.id',
                'Tag', 
                'User.username'
        )
);
$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:

Array

(

    [Thing] => Array

        (

            [id] => 1

            [name] => Microsoft PowerPoint

            [slug] => microsoft_powerpoint

            [description] => Slideshow generation software.

            [user_id] => 1

            [is_visible] => 1

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

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

        )

 
    [User] => Array

        (

            [username] => paulherron

        )

 
    [Deal] => Array

        (

            [0] => Array

                (

                    [id] => 1

                    [title] => Buy PowerPoint Cheap!

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

                    [user_id] => 1

                    [thing_id] => 1

                    [User] => Array

                        (

                            [username] => paulherron

                        )

 
                )

 
        )

 
    [Image] => Array

        (

            [0] => Array

                (

                    [id] => 1

                    [thing_id] => 1

                )

 
            [1] => Array

                (

                    [id] => 2

                    [thing_id] => 1

                )

 
            [2] => Array

                (

                    [id] => 3

                    [thing_id] => 1

                )

 
        )

 
    [Tag] => Array

        (

            [0] => Array

                (

                    [id] => 1

                    [tag] => Slideshow software

                    [slug] => slideshow_software

                    [is_visible] => 1

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

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

                    [TagsThing] => Array

                        (

                            [id] => 1

                            [tag_id] => 1

                            [thing_id] => 1

                            [user_id] => 1

                            [is_visible] => 1

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

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

                        )

 
                )

 
        )

 
)

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

Leave a Comment

Your Name

Your Website

Enter the characters shown in the image below
Not case sensitive.[Refresh]

Your Comment

« Articles