Calculating AVG() for a not directly related model

Yii 1.1 General Discussion
1

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!

Simplicissimus

2

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

3

Here we go:




select AVG(tbl_entry.rating) as rating

from tbl_category 

join tbl_subcategory on tbl_category.id=tbl_subcategory.category_id

join tbl_entry on tbl_subcategory.id=tbl_entry.subcategory_id

where tbl_category.id=:id and tbl_entry.author_id=:author_id

catgory.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,

Simplicissimus