Filtering by HABTM model count

My problem: Show the user 4 Albums where each Album has >= 1 Images.

I started this project in CakePHP 1.1 and realized quickly I couldn't
create an elegant solution, so I've recently upgraded to 1.2.2 and am
trying again.

My code below works correctly and returns a wonderful array with all
the pieces of data I need. The really odd thing is that after Cake has
sent the first query, joining all the necessary tables, it then goes
back to query the Image model 4 times, each time failing because it
attempts to use the non-joined AlbumsImage HABTM association in the
where clause.

Here's the error and offending SQL:

Warning (512): SQL Error: 1054: Unknown column 'AlbumsImage.image_id'
in 'where clause' [CORE/cake/libs/model/datasources/dbo_source.php,
line 525]

SELECT `image`.`id`,`image`.`title`,`image`.`host`,`image`.`path`
FROM `images` AS `image`
WHERE `image`.`status` = 0
AND `image`.`id` = `albumsimage`.`image_id`
ORDER BY `image`.`created` ASC

I don't know why this query is even being issued, but as I say it's
all the more odd because the main query (below) returns successfully.

SELECT
`album`.`id`,`album`.`user_id`,`album`.`title`,USER.id,USER.username,`user`.`id`
FROM `albums` AS `album`
LEFT JOIN `users` AS `user`
ON (`album`.`user_id` = `user`.`id`)
LEFT JOIN `albums_images` AS `albumsimage`
ON (`albumsimage`.`album_id` = `album`.`id`)
INNER JOIN `images` AS `image`
ON (`image`.`status` = 0
AND `image`.`id` = `albumsimage`.`image_id`)
WHERE `album`.`status` = 0
GROUP BY `album`.`id`,`album`.`title`
HAVING Count(* ) >= 1
ORDER BY `album`.`created` DESC,`image`.`created` ASC
LIMIT 4

Here is the method of the Album model I use to query Albums where
Image count >= 1:

function gimme($num=6) {
$contain = array(
'AlbumsImage',
'Image'=>array('ImageSize',
'fields'=>array
('Image.id','Image.title','Image.host','Image.path'),
'order' => 'Image.created ASC'
),
'User'=>array('fields'=>'User.id,User.username'));

$this->resetAssociations(); // just in case

$this->bindModel(array('hasOne'=>array('AlbumsImage',
'Image'=>array(
'className'=>'Image',
'foreignKey'=>false,
'type'=>'INNER',
'conditions'=>array(
'Image.status =
0',
'Image.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'))); // we only want
Albums with 1 or more images
return $albums;
}

I've been trying to figure this out all day so would /really/
appreciate any insights into this weird SQL error.

Thanks everyone!
-volve
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups "CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to cake-php+unsubscribe@googlegroups.com
For more options, visit this group at http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---