Help with SQL to ActiveRecord

currently I have in my search model:

$query = Faq::find();

However I need a calculated column that shows an average rating from another table. The following SQL returns what I need:


FROM faq as a

inner join 

(SELECT faq_id, AVG(faq_rating) as 'average_rating'

FROM faq_rating

GROUP BY faq_id) as b


Is it possible to do the above query in Active Record? If I can use ActiveRecord, then the rest of my implementation seems easy, I’m substituting the original query that I listed above. If I can’t do that, I may be able to use SqlDataProvider to get what I want, but that would mean completely rewriting my search method, so I’m trying to avoid that if I can.

Any help with this would be appreciated.

I’m absolutely not sure if this is the right way to do it, bu here’s what’s working for me: If I need the result of an aggregate function injected into an AR’s property, I create said property (as in: I really define it in the model) and modify the [font=“Courier New”]select()[/font] statement so the result of the aggregate function is mapped to the properties name.

In your case, your [font="Courier New"]Faq[/font] model would get a new [font="Courier New"]average_rating[/font] property:

class Faq extends \yii\db\ActiveRecord {



    * @var integer


   public $average_rating;

I would then select the models as follows:

$faqs = Faq::find()

    ->select(['faq_id', 'AVG(faq_rating) AS average_rating'])




This assumes your [font="Courier New"]Faq[/font] model has a relation called ratings set up.

Again, absolutely not sure if that’s the intended way or not, but it works.

Since you mention a dataProvider, you probably are using it with a GridView or ListView.

Check if this web tip helps your case. The web tip shows an example of using SUM for summary - it can be replaced with AVG.

@Da:Sourcerer, @Kartik Great tips guys. I will work through them and update this post. Just wanted to say thanks now for the lightning fast reply…


I chose Kartik’s solution due to the fact I was using Gridview and also my search method is setup based on his tutorial, so it made sense to try to plug in this solution as well.

I was able to implement Kartik’s solution quickly and it works nicely and this is a great lesson for returning a calculated value into Gridview and keeping it sortable.

One gotcha for AVG/average. In MySql, the function is AVG. In Yii 2, it’s average(). Example:

public function getAverageRating()


    return $this

        ->hasMany(FaqRating::className(), ['faq_id'=>'id'])



So when you are creating the base model method, use average(). In the query string in the search model, use AVG, like so:

   public function search($params)



        $query = Faq::find();


        $subQuery = FaqRating::find()

        ->select('faq_id, AVG(`faq_rating`) as average_rating')





              ], 'faqAverage.faq_id =');




        $dataProvider = new ActiveDataProvider([

            'query' => $query,



Thanks again for the help.