Help with SQL to ActiveRecord

Yii 2.0 General Discussions
1

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:







SELECT *

FROM faq as a

inner join 

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

FROM faq_rating

GROUP BY faq_id) as b

ON a.id=b.faq_id;

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.

2

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'])

    ->joinWith('ratings')

    ->groupBy('faq.faq_id')

    ->all();

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.

3

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.

4

@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…

UPDATE

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'])

        ->average('faq_rating');

}

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

        ->groupBy('faq_id');

         

         $query->leftJoin([

                 'faqAverage'=>$subQuery

              ], 'faqAverage.faq_id = faq.id');

        

        

        

        $dataProvider = new ActiveDataProvider([

            'query' => $query,

           

        ]);

Thanks again for the help.