Laravel eloquent GROUP BY

Question

I have four tables: supplies, stocks, stock_ins, and stock_outs. In the Stock controller, I want to sum the total stock_out for each supply .

For this, I have used the following query:

 $supplies = Stock::select(
            'supplies.name', 
            'stocks.supply_id',
            'stocks.id',
            'stocks.stock',
            'stock_ins.quantity as stock_in_quantity',
            DB::raw('SUM(stock_outs.quantity) as stock_out_quantity')
        )
        ->join('supplies', 'stocks.supply_id', '=', 'supplies.id')
        ->leftJoin('stock_ins', 'stocks.supply_id', '=', 'stock_ins.supply_id')
        ->leftJoin('stock_outs', 'stocks.id', '=', 'stock_outs.stock_id')
        ->groupBy('stocks.supply_id', 'supplies.name' , 'stocks.stock','stock_ins.quantity','stocks.id') 
        ->get();

However, I'm getting the following error:

SQLSTATE[42000]: Syntax error or access violation: 1055 'galaxy.supplies.name' isn't in GROUP BY

I've also tried to run the raw query in MySQL, which is showing the desired result:

SELECT
  supplies.name,
  stocks.supply_id,
  stocks.id,
  stocks.stock,
  stock_ins.quantity AS stock_in_quantity,
  SUM(stock_outs.quantity) AS stock_out_quantity
FROM
  stocks
  INNER JOIN supplies ON stocks.supply_id = supplies.id
  LEFT JOIN stock_ins ON stocks.supply_id = stock_ins.supply_id
  LEFT JOIN stock_outs ON stocks.id = stock_outs.stock_id
GROUP BY
  stocks.id

Answer 1

Since the raw query in MySQL is running and shows you the desired result, you can replicate this behavior in your Laravel application by turning off strict mode. in config\database.php add strict=>false.

     'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            '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' => '',
            'prefix_indexes' => true,
            'strict' => false, //🟢 this line
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

then update your group by stocks.id as in your raw sql query

        ->groupBy('stocks.id')