mysql - sql 不在 GROUP BY 中使用查询生成器 laravel

Question

我想根据从表投票中取出的 product_id 取多少数据。在 laravel 上这个查询不能很好地工作但是当我在 mysql 中尝试它时我运行它很好还有其他解决方案吗？

这是通过 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 上进行的查询:

$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]: 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

我必须使用 group by product_id 来根据投票中的 product_id 数量计算多少数据量。

Answer 1

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

进入 config/database.php 设置 strict=>false 就可以了。

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