使用查询生成器Laravel，SQL不在GROUP BY中 [英] sql isn't in GROUP BY using query builder laravel

问题描述

我想根据从表投票中获取的product_id来获取多少数据.在laravel上，此查询无法正常运行，但是当我在mysql中尝试运行时，它运行正常吗，是否还有其他解决方案?

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?

这是通过mysql成功执行的查询:

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

结果:

这是我使用查询生成器使用的关于laravel的查询:

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

我收到此错误:

"SQLSTATE [42000]:语法错误或访问冲突:1055 'lookbubbledrink.v.id'不在GROUP BY中(SQL:select count()as 总计v.，p.name从votes作为v左连接products p上的p.id =(从产品中选择p1.id作为p1，其中v.product_id = p1.id)按v.product_id

"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

我只需要使用product_id分组来根据投票中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.

推荐答案

您在laravel中使用严格模式.您可以根据自己的需要进行调整.

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

进入设置strict=>false中的config/database.php，您就很好了.

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

这篇关于使用查询生成器Laravel，SQL不在GROUP BY中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！