thank you so much for your post. I digged deeper into it and you are right. The best is to create it via an query.
But by doing this, I found a strange behaviour of yii (at least I can not understand it)…
So my tasks stays the same, as written in my first post.
My query is this now:
$criteriaKey =new CDbCriteria();
$criteriaKey->select='imageKeywords.keyword_id as keyword_id, count(imageKeywords.keyword_id) as keyCount';
$images = Image::model()->findAll($criteriaKey);
Looking into the log file of yii, I can see that it creates the right SQL:
SELECT imageKeywords.keyword_id as keyword_id,
count(imageKeywords.keyword_id) as keyCount, `imageKeywords`.`id` AS
`t1_c0`, `imageKeywords`.`image_id` AS `t1_c1`,
`imageKeywords`.`keyword_id` AS `t1_c2` FROM `image` `t` LEFT OUTER JOIN
`image_keyword` `imageKeywords` ON (`imageKeywords`.`image_id`=`t`.`id`)
WHERE ( MATCH (`search_criteria`) AGAINST (' +1412277* ' IN BOOLEAN MODE))
GROUP BY imageKeywords.keyword_id
If I copy this into my MYSQL directly, I get what I want: (I have removed some columns)
BUT: Yii is not returning this the same way.
Only the first keyword_id is printed out with the keycount of 2. All following keyword_ids get a keyCount of 1.
To dig deeper, I have made a print_r($images) to see what the resulting array is that yii uses. For my surprise, only the first image has one keyCount, the sub-array with all the keywords do not have any counts at all.
I dont understand that. As the SQL created by yii looks good, this must be related to how it uses this activeRecord.
Can somebody explain me this?
Is ther a solution?
Well, I could stop for this with AR, and just use plain SQL, but as you see above, there is a "merge" in the criteria, as I do get lots of criteria from another AR Query, so I need AR or I would loose all those settings.
hmmmm. Any ideas?