Calculating AVG() for a not directly related model

I am new to the Yii framework I have still some problms understanding everything.

Suppose I have the following setup:

Category -has many-> SubCategory -has many-> Entry (Attributes: rating, author)

Now I want to

calculate for Category1 the average rating for Entries by Author1.

in SQL it is easy. Whats the right way ™ to do such a thing with Yii typical concepts like CActiveRecord realtions() and/or CActiveRecord find().

Thanks in advance. With best regards!


Can you show the SQL you’d like to get in the end?

Here we go:

select AVG(tbl_entry.rating) as rating

from tbl_category 

join tbl_subcategory on

join tbl_entry on

where and tbl_entry.author_id=:author_id comes from the current model object. author_id should come as parameter.

The use case is the following:

I want to print the average rating for each category for every author:

        Author1 Author2 AuthorN

Category1  7       2      ...

Category2  4       4      ...

CategoryN ...     ...     ...

So i would like do the following (in pseudocode here):

foreach (Category::model()->findAll() as $category) {

    foreach (Auhor::model()->findAll() as $author) {

        echo $category->scopeFunctionForAuthor($author->id)->rating;



Thanks in advance. Regards,