CODEWITHSUNDEEP
mysqllaravel
user9535105
user9535105

Reputation:

sql isn't in GROUP BY using query builder laravel

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.

Upvotes: 2

Views: 9245

Answers (1)

FULL STACK DEV
FULL STACK DEV

Reputation: 15971

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

Upvotes: 16

Related Questions