sql isn't in GROUP BY using query builder laravel

Question

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 :

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.

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. — Uueerdo, Jul 16 '18 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 — Raymond Nijland, Jul 16 '18 at 16:49
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. — Raymond Nijland, Jul 16 '18 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? — Uueerdo, Jul 16 '18 at 16:52
Yes but you need to add in a example data place it on sqlfiddle.com or db-fiddle.com and add in the expected output as ascii data table (text formatted) — Raymond Nijland, Jul 17 '18 at 10:44
Does this answer your question? [Group by not working - Laravel](https://stackoverflow.com/questions/41571271/group-by-not-working-laravel) — miken32, Jan 30 '20 at 22:17

score 14 · Accepted Answer · answered Jul 17 '18 at 05:00

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

sql isn't in GROUP BY using query builder laravel

1 Answers1

Related