Can't convert SQL query to laravel eloquent

Question

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();
 }
}

Please can you show the relationship for votes and also the table definitions. — Rwd
– Rwd, Commented Jan 11, 2020 at 13:44

d3jn · Accepted Answer · 2020-01-11 14:40:21Z

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

Rwd · Accepted Answer · 2020-01-11 14:24:10Z

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
      }
   ]
}

Collectives™ on Stack Overflow

Can't convert SQL query to laravel eloquent

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related