anomalous territory

The problem: Your models look like this “Images HABTM Albums” so when you want to show 4 Albums, they come along with their Images too – great! BUT, what happens if some Albums don’t have any Images? You don’t want to show those to your users, but how do you filter them out? Well you could do it after the find() has run, in which case you may end-up with less than 4 useable Albums – bad To workaround that you could always ask find() to give you 8 Albums in the hope that at least 50% of them will be useable, but there’s no guarantee – still bad.

The solution below takes advantage of CakePHP 1.2’s bindModel() method to manually create an INNER JOIN in place of the pre-defined HABTM association. This allows us to execute the find() with a HAVING COUNT(*) in the group option. In the example below, this causes any Albums with 0 Images to be omitted from the results as they don’t meet the HAVING criteria. What’s more, the limit of 4 correlates exactly so 8-just-incase kludge needed.

This was driving me crazy so here’s the solution for anyone else that might be stuck in the same situation. (This method is in the Albums model.)

  function getAlbums($num=4) {
    $contain = array( // use Containable Behavior for efficiency      'AlbumsImage',
      'FilterImage',// although part of the magic and required, this seems to confuse CakePHP a little - I'll try and resolve it and update the post
      'Image',
      'Image'=>array('ImageSize',
                     'fields'=>array('Image.id','Image.title','Image.host','Image.path')),
      'User'=>array('fields'=>'User.id,User.username'));

    $this->bindModel(array('hasOne'=>array('AlbumsImage',
                                           'FilterImage'=>array( // FilterImage so as to distinguish between regular Image
                                                 'className'=>'Image',
                                                 'foreignKey'=>false,
                                                 'type'=>'INNER',
                                                 'conditions'=>array(
                                                     'FilterImage.status = 0',
                                                     'FilterImage.id = AlbumsImage.image_id')
                                                 )
                                           )
                           ));

    $albums = $this->find('all', array('contain' => $contain,
                                    'conditions' => array('Album.status'=>'0'),
                                        'fields' => array('Album.id','Album.user_id','Album.title'),
                                         'order' => 'Album.created DESC',
                                         'limit' => $num,
                                         'group' => array('Album.id','Album.title HAVING COUNT(*) >= 1'))); // here's the clincher

    return $albums;
  }