Self:stat Get Counts Of Related Table Per Category In Realted Table

Yii 1.1 General Discussion
1

Hello out there,

I am a bit stuck with the following problem.

Lets say I have two tables, Posts and Comments. There is a 1:N between the two.

The Comments table has the following columns:

id / post_id / comment / category_id

Now I have a query that returns 10 Posts.

The goal is that I get a result like that (within the 10 posts result page):

2 comments of category 1

5 comments of category 4

etc. So a summary of categories for all 10 posts.

Right now I do use two loops, like for for cycling through those 10 posts.

The other one for cycling through all comments per post and getting the categories, counting them up.

This works, but as I have thousands of comments, this takes way too long. :blink: :blink:

Can this be solved with the yii self:stat relations?

I tried it but I dont get it working.

Any ideas?

gb5256

2

Hi gb5256,

Please check this wiki:

http://www.yiiframework.com/wiki/319/searching-and-sorting-by-count-of-related-items-in-cgridview

3

Hmmm, that post is not what I want (or I dont get it…).

Your solution would give as a result something like a total number of comments per post.

But I need it per category per comment.

(and I am not talking about the gridview, its a filtered index view).

So in the sidebar of the list of all posts, there should be a summary of ALL comments of this posts.

In SQL it is this what I want:

SELECT a.id, count(b.category_id)

FROM posts a INNER JOIN comments b

ON a.id = b.post_id where a.name = "xxx" GROUP BY b.category_id

Are we talking about the same thing?

4

Oh, I’m sorry. Please forget the previous post.

I didn’t notice that the summary was for the entire 10 posts. :-[

I would try something like this:




select count(id), category_id from comments where post_id in (xxx) group by category_id;

where xxx is the ids of the main query result.

I don’t think STAT relation could help you a lot for this.

5

Hi softark,

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->mergeWith($criteria);

$criteriaKey->select='imageKeywords.keyword_id as keyword_id, count(imageKeywords.keyword_id) as keyCount';

$criteriaKey->group='imageKeywords.keyword_id';

$criteriaKey->order='';

$criteriaKey->with=array('imageKeywords');

$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)

ID-----keyword_id—keyCount

152728–6-----------2

152721–11----------1

152727–111---------1

152727–14----------1

152725–153---------9

152727–153---------1

152726–177---------10

152727–243---------1

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?

gb5256

6

I think the problem is in the merged criteria that you use for the main query. My guess is that it is filtering the imageKeywords relations from the result set, although it is not very clear in the main query result.

7

Hello Softark,

I ended up using query builder. The result of query builder is just plain what you get from SQL direcly, so this is what I want. The criteria I pass are fine, as seen in the log file of yii, so this must be AR related.

Thank you very much for sharing your ideas about this.

Very much appreciated.

gb5256

PS: But by doing all of this I encounter a new query problem, but this will be a new post…

Thanks again…