Hi!
Id like to filter my videos by tags. The video must have all of the
tags, and I want to sort the result by count on the stats table.
I've got a working version without stats allthought this is not
working with paginate.
$searchTerms = array('Tag1','Tag2');
$numCount = count($searchTerms);
$contain = array('FilterTag','TagsVideo');
$this->Video->bindModel(array('hasOne'=>array(
'TagsVideo',
'FilterTag'=>array(
'className'=>'Tag',
'foreignKey'=>false,
'type'=>'INNER',
'conditions'=>array(
'FilterTag.id = TagsVideo.tag_id',
'FilterTag.name' => $searchTerms
)
)
)
)
);
$newest = $this->Video->find('all',array('contain' => $contain,
'order' => 'Video.created DESC',
'group' => array(
'Video.id',
'Video.title HAVING COUNT(FilterTag.name) = ' . $numCount
),
'limit' => $limit,
'conditions' => array('Video.published' => 1)
)
);
Here is my custom query which is working. Is there a way to use
cakephp core functions to achieve this?
SELECT `Video`.*, `TagsVideo`.*, `FilterTag`.`id`, `FilterTag`.`name`,
Stat.hits FROM `mp_videos` AS `Video`
LEFT JOIN (SELECT COUNT(*) as hits, mp_stats.video_id,
mp_stats.channel_id FROM mp_stats WHERE mp_stats.channel_id = ".
$channelid." GROUP BY mp_stats.video_id ) as Stat ON Stat.video_id =
Video.id
INNER JOIN `mp_tags_videos` AS `TagsVideo` ON (`TagsVideo`.`video_id`
= `Video`.`id`)
INNER JOIN `mp_tags` AS `FilterTag` ON (`FilterTag`.`id` =
`TagsVideo`.`tag_id` AND `FilterTag`.`name` IN (" . $tags ."))
WHERE Video.published = 1 GROUP BY Video.id, Video.title HAVING COUNT
(*) = ".$numCount." ORDER BY hits DESC LIMIT " . $limit;
Thanks for any help and hints,
sucram
Check out the new CakePHP Questions site http://cakeqs.org and help others with
their CakePHP related questions.
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+unsubscr...@googlegroups.com For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
