Laravel Question posted in
The official Laravel Documentation can be found here.

Laravel eloquent GROUP BY

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
Tags:

2

Answers


  1. 0

    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 configdatabase.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')
    Login or Signup to reply.
  2. 0

    You can change this strict mode in run time as below.

    config()->set('database.connections.mysql.strict', false);
DB::reconnect();

    Your query…

        config()->set('database.connections.mysql.strict', true);
DB::reconnect();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search