Sunday, April 16, 2017

Laravel Calculate AVG of a related column

I am trying to calculate average ratings of a related column. The relationship (1->many) is in between Merchant & ClientFeedbackReviews Models.

Merchant Model

[Merchant Model]
::::::::::::::::::::::::::::

public function clientFeedbacks() {
    return $this->hasMany('App\ClientFeedbackReviews'); //, 'merchant_id', 'id');

ClientFeedbackReviews

 ::::::::::::::::::::::::::::::::::::::: 

class ClientFeedbackReviews extends Model {

protected $table = 'client_feedback_merchants';
public function forMerchant() {
    return $this->belongsTo('App\Merchant', 'merchant_id', 'id');
}

public function byClient() {
    return $this->belongsTo('App\Clients', 'client_id', 'id');
}
:::::::::::::::::::::::::::::::::::::

I need to get average ratings of all the merchant (as a PART of a query where I am calculating results-the merchants according to nearby distances, given location or a using search string depending upon the Request Data)

I have tried almost everything I found on internet but couldn't able to get 'average_ratings' key-value in results.

Here is one the many solutions that I have tried and pasting here as an example

 /////// query continued //////////

 $getMerchantQuery = $getMerchantQuery->with(['clientFeedbacks' => function($query) {

            $query->select(DB::raw('AVG( stars) AS average_rating'));
        }]);

 /////// query continued //////////

and this the what I am getting ALWAYS - empty array for client_feedbacks whereas I want the average_rating right there.

  {
"id": 1,
"user_id": 2,
"company_name": "Best Salon",
"primary_contact": "111111111",
"company_st_address": "Office # 62",
"company_location": "Abc",
"company_city": null,
"company_state": null,
"company_country": null,
"company_zip": null,
"company_lat": "27.9506",
"company_long": "82.4572",
"logo_image": "449cbdf0-12ba-11e7-bc65-b7fa1731e4d5.jpeg",
"is_fav_by_client_count": 3,
"client_feedbacks_count": 1,
"user_details": {
  "id": 2,
  "email": "client@lir.com"
},
"client_feedbacks": []

}

What are the options do we have to calculate avg of a related table ?

============ EDIT

However this returns results but not sure how to get the AVERAGE in client_feedbacks key.

      $getMerchantQuery = $getMerchantQuery->with('clientFeedbacks');

as

{
"id": 1,
"user_id": 2,
"company_name": "Best Salon",
"primary_contact": "111111111",
"company_st_address": "Office # 62",
"company_location": "abc",
"company_city": null,
"company_state": null,
"company_country": null,
"company_zip": null,
"company_lat": "27.9506",
"company_long": "82.4572",
"logo_image": "449cbdf0-12ba-11e7-bc65-b7fa1731e4d5.jpeg",
"is_fav_by_client_count": 3,
"client_feedbacks_count": 1,
"user_details": {
  "id": 2,
  "email": "client@lbb.com"
},
"client_feedbacks": [
  {
    "id": 1,
    "client_id": 1,
    "merchant_id": 1,
    "stars": 4,
    "review_notes": "good client",
    "created_at": null,
    "updated_at": null
  }
]

}



via Zeshan

Advertisement