Filtering results returned by Containable behavior

First of all, let me warn you that what I’m describing here is more or less a hack and is not the most efficient way of handling such things, especially if you are dealing with large volumes of data. It is really here for educational purposes and as food for thought…

Now that I got that out of the way, let’s use our favorite Post HABTM Tag example, and try to do something like this…

We need to get all Posts whose titles start with a ‘t’ and all related Tags, which start with an ‘n’.

This seems like a perfect job for the Containable behavior:

$this->Post->find('all', array(
                                   'conditions'=>array('Post.title LIKE'=>'t%'),
                                   'contain'=>array('Tag'=>array(
                                                              'conditions' => array('Tag.tag LIKE'=>'n%')
                                   ))));

The problem is that we will get back all Posts that start with a ‘t’, but if there is no matching Tag that starts with an ‘n’ we will simply get an empty array.

The returned data might looks something like:

Array
(
    [0] => Array
        (
            [Post] => Array
                (
                    [id] => 4
                    [title] => test
                    [post] =>
                    [created] => 2008-06-20 16:06:53
                    [modified] => 2008-06-20 16:06:53
                    [user_id] => 0
                )

            [Tag] => Array
                (
                )

        )

    [1] => Array
        (
            [Post] => Array
                (
                    [id] => 99
                    [title] => testing 00777
                    [post] =>
                    [created] => 2008-07-18 15:14:24
                    [modified] => 2008-07-18 15:14:24
                    [user_id] => 0
                )

            [Tag] => Array
                (
                    [0] => Array
                        (
                            [id] => 12
                            [tag] => new
                            [status] => 0
                            [PostsTag] => Array
                                (
                                    [id] => 159
                                    [post_id] => 99
                                    [tag_id] => 12
                                    [status] =>
                                )

                        )

                )

        )

Well, the second record looks like what we really need, but we certainly don’t need the first one.

Let’s use our Post model’s afterFind() method to filter out unwanted data:

[sourcecode language=”javascript”]

function afterFind($results, $primary=false) {
if($primary == true) {
foreach($results as $key => $value) {
if(empty($value[‘Tag’])) {
unset($results[$key]);
}
}
}

return $results;
}
[/cc]

Yeah, not the prettiest solution by any means, but it gets the job done relatively painlessly.

Remember that afterFind() will be executed after each find() call, so be mindful of what you are doing, because you may tamper with results that really did not need any filtering.

m4s0n501
  • LarryZen

    You save my day.

    Cakephp forces us to tweak lot things to see it works as espected. Weird.

  • http://teknoid.wordpress.com teknoid

    @LarryZen

    I’m glad it helped. Regarding the second point, that’s not really true. To get more accurate results you could force a JOIN, which is outlined in some other posts on my blog. That being said, a little filter like this one takes almost no time to implement, but think about how much time you’ve saved just by relying on cake’s ORM alone.
    The trade-off is minimal. To say cake forces you to do something is simply not fair, cake offers you to follow best practices, and sometimes it can be hard to “unlearn” bad habits.

  • flaccid

    Thank you for this simple solution!

    I do hope that CakePHP will simply support associated table conditions in find() one day as currently this limitation defeats the point of MVC and RDBMS.

  • http://teknoid.wordpress.com teknoid

    @flaccid

    There are other ways to handle it, check out the article on bakery for doing ad-hoc joins, the Joinable behavior and forcing a JOIN posts on this blog.

  • flaccid

    Thanks teknoid. However all these methods are still defeating the whole purpose of modeling. We are encouraged to write well-defined models and then have to do more controller code anyway.

    Is one of the methods you mentioned there simply forcing joins on a find?
    Could you please link directly to these articles as I’m unsure exactly which ones you mean.

  • http://teknoid.wordpress.com teknoid

    @flaccid

    Linkable behavior:
    http://github.com/rafaelbandeira3/linkable/tree/master
    http://rafaelbandeira3.wordpress.com/2008/11/16/linkable-behavior-taking-it-easy-in-your-db/

    The rest you can search right on this blog or google ;)

    The above solution requires nothing special in the controller your modeling will remain exactly the same (if done correctly).