CODEWITHSUNDEEP
phplaraveleloquent
Joseph
Joseph

Reputation: 6269

Can't convert SQL query to laravel eloquent

I have this query that will get how many user votes with each star number

SELECT stars, COUNT(*) AS rate FROM product_user where product_id = 1 GROUP BY(stars)

result of this query

stars | rate
_____________
 2    |  3
 5    |  4

but I can't convert it to laravel eloquent

this is my try but it gets an error

Product::find($id)->votes()->selectRaw('count(*) as rate, stars')
  ->groupBy('stars')
  ->get();

Error message

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'roya.users.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select `users`.`id`, `first_name`, `last_name`, count(*) as rate, stars, `product_user`.`product_id` as `pivot_product_id`, `product_user`.`user_id` as `pivot_user_id`, `product_user`.`stars` as `pivot_stars`, `product_user`.`feedback` as `pivot_feedback`, `product_user`.`created_at` as `pivot_created_at`, `product_user`.`updated_at` as `pivot_updated_at` from `users` inner join `product_user` on `users`.`id` = `product_user`.`user_id` where `product_user`.`product_id` = 1 group by `stars`)

Product Model

class Product extends Model {
 public function votes()
 {
        return $this->belongsToMany(User::class)
            ->using('App\Review')
            ->select('users.id', 'first_name', 'last_name')
            ->withPivot(['stars', 'feedback'])
            ->withTimeStamps();
 }
}

Upvotes: 0

Views: 355

Answers (2)

d3jn
d3jn

Reputation: 1410

When you use grouped select queries SQL allows only for aggregated functions or columns specifically listed in GROUP BY section to be selected. Your votes() relationship adds extra select columns to your query ('users.id', 'first_name' and 'last_name') and they are causing an error. This happens because selectRaw method doesn't replace previously selected columns, but utilizes addSelect() method to add raw on top of the existing ones.

In your case it's really cumbersome to use Eloquent here when you only need an aggregated count data for specific product votes.

Just add getVotesCountByStars method to your Product model and utilize Laravel's generic query builder via DB facade:

public function getVotesCountByStars()
{
    return DB::table('product_user')
        ->where('product_id', $this->id)
        ->selectRaw('count(*) as rate, stars')
        ->groupBy('stars')
        ->orderBy('stars', 'asc')
        ->get()
        ->pluck('rate', 'stars')
        ->toArray();
}

This way you will see exactly what query is generated and no additional overhead is produced (in my example, an associative array with stars as keys and counts as values will be returned).

Upvotes: 2

Rwd
Rwd

Reputation: 35170

What you could do is is add a ratings relationship to your Product model that is a hasMany between Product and Rating:

public function ratings()
{
    return $this->hasMany(Rating::class)
        ->select('stars', 'product_id')
        ->selectRaw('count(*) as rate')
        ->groupBy('stars', 'product_id');
}

Then your query would be something like:

$product = Product::with('rating')->find(1);

This would product something like:

{
   "id":1,
   ...
   "ratings":[
      {
         "stars":2,
         "product_id":1,
         "rate":1
      },
      {
         "stars":3,
         "product_id":1,
         "rate":2
      },
      {
         "stars":4,
         "product_id":1,
         "rate":4
      },
      {
         "stars":5,
         "product_id":1,
         "rate":3
      }
   ]
}

Upvotes: 1

Related Questions