2

enter image description here I want to take how much amount of data based on the product_id taken from the table vote. on laravel this query does not work well but when i try it in mysql i run it fine is there any other solution?

this is a successfully executed query through mysql :

select count(product_id) as total, `v`.*, `p`.`name` from `votes` as `v` left join `products` as `p` on `p`.`id` = (select p1.id from products as p1 where v.product_id = p1.id ) group by v.product_id

result : enter image description here

and it's a query on laravel that I use using query builder : 

$count = DB::table('votes as v')->leftJoin('products as p',function($join){
  $join->on('p.id','=',DB::raw('(select p1.id from products as p1 where v.product_id = p1.id )'));
})->select(DB::raw('count(*) as total'),'v.*','p.name')->groupBy('v.product_id')->get();
dd($count);

and i got this error :

"SQLSTATE[42000]: Syntax error or access violation: 1055 'lookbubbledrink.v.id' isn't in GROUP BY (SQL: select count() as total, v., p.name from votes as v left join products as p on p.id = (select p1.id from products as p1 where v.product_id = p1.id ) group by v.product_id

I have to use group by product_id only to calculate how much amount of data based on the number of product_id in the vote.

Improve this question
8
  • Configurations in newer versions of MySQL disable the partial group by uniquely permitted by MySQL. In such configurations, all non-aggregated fields selected must be included in the group by clause. Since you're selecting v.* you will need to include every field from v in the group by. Commented Jul 16, 2018 at 16:48
  • "on laravel this query does not work well but when i try it in mysql i run it fine" On MySQL it also does NOT run fine your query will generate wrong results..Laraval most likely set the ONLY_FULL_GROUP_BY sql_mode active for the php mysql client Commented Jul 16, 2018 at 16:49
  • is there any other way to generate such data? @Uueerdo Commented Jul 16, 2018 at 16:51
  • Use a delivered table (subquery) to count and group by product_id and inner join that result set back with the other data only possible way. Commented Jul 16, 2018 at 16:52
  • Including v.* in such a query doesn't even really make much sense; * from which v row of the numerous ones that could have the same product_id? Commented Jul 16, 2018 at 16:52

1 Answer 1

Reset to default
16

You are using strict mode in laravel. By the you can tweak it to your needs.

got to config/database.php in set strict=>false and you are good to go.

  'mysql' => [
            'driver' => 'mysql',
            'host' => env('DB_HOST', '127.0.0.1'),
            'port' => env('DB_PORT', '3306'),
            'database' => env('DB_DATABASE', 'forge'),
            'username' => env('DB_USERNAME', 'forge'),
            'password' => env('DB_PASSWORD', ''),
            'unix_socket' => env('DB_SOCKET', ''),
            'charset' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'strict' => false,
            'engine' => null,
        ],
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.