Clever Geek Handbook

How can I solve inconsistent sql_mode = only_full_group_by in laravel eloquent?

My eloquent laravel looks like this:

$products = Product::where('status', 1)
            ->where('stock', '>', 0)
            ->where('category_id', '=', $category_id)
            ->groupBy('store_id')
            ->orderBy('updated_at', 'desc')
            ->take(4)
            ->get();

There is an error like this on execution:

SQLSTATE [42000]: Syntax error or access violation: 1055 Expression # 1 of the SELECT list is not in the GROUP BY clause and contains an unaggregated column 'myshop.products.id', which is functionally independent of the columns in the GROUP BY clause; this is incompatible with sql_mode = only_full_group_by (SQL: select * from products

where status

= 1 and stock

> 0 and category_id

= 5 group store_id

order updated_at

desc limit 4)

How can I solve it?

+12


source to share


11 replies


I had a similar problem and solved it by disabling mysql strict mode in the database connection settings.

'connections' => [
    'mysql' => [
        // Behave like MySQL 5.6
        'strict' => false,

        // Behave like MySQL 5.7
        'strict' => true,
    ]
]


You can find even more config settings in this blog post by Matt Stauffer

+32


source


In config => database.php folder make sure mysql strict is false like this

'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' => 'utf8',
    'collation' => 'utf8_general_ci',
    'prefix' => '',
    'strict' => false,
    'engine' => null,
],


if true is set to true, set it to false, then clear config money by running this command in cmd

PHP Artisan Configuration: Clear

+6


source


I solved this problem by adding the "mode" option and setting only those modes that I want to enable in config => database.php

'mysql' => [
    ...
    'modes' => [
        'STRICT_ALL_TABLES',
        'ERROR_FOR_DIVISION_BY_ZERO',
        'NO_ZERO_DATE',
        'NO_ZERO_IN_DATE',
        'NO_AUTO_CREATE_USER',
    ],
],

More in this tutorial

+6


source


This is because recent MySQL versions behave like most dbms already in terms of offerings group by

; general rule

if you are using group by

all the columns in the select

need to be present in group by

or aggregate aggregation function ( sum

, count

, avg

etc.),

Your current request is grouped with using store_id

, but since you select everything the above rule is not followed.

+5


source


The file .env the ADD variable: DB_STRICT=false

.

AND REPLACE in file from location: config / database.php , following codes are 'strict' => true

ON 'strict' => (env('DB_STRICT', 'true') === 'true'? true: false)

,

good luck.

+5


source


Check the request:

Product::where('status', 1)
            ->where('stock', '>', 0)
            ->where('category_id', '=', $category_id)
            ->groupBy('store_id')
            ->orderBy('updated_at', 'desc')
            ->take(4)
            ->get();

here you are grouping the data with store_id

and retrieving all columns in the result set that are not allowed. To fix this problem, select either store_id

the aggregation function on it or change the system variable sql_mode=only_full_group_by

to SET sql_mode = ''

.

Link

+1


source 


 #Have the following method in your helper file
if (!function_exists('set_sql_mode')) {
/**
 * @param string $mode
 * @return bool
 */
function set_sql_mode($mode = '')
{
    return \DB::statement("SET SQL_MODE=''");
}
}

Then call set_sql_mode (''); shortly before eloquent / request

+1


source


set

'strict' => false

in your config / database.php file. In an arrayconnections => mysql =>

in my case i am using mysql 5.7 Laravel 5.7

+1


source


To select only aggregated columns use one of the raw methods provided by Laravel . For example:

Product::selectRaw('store_id')
        ->where('status', 1)
        ->groupBy('store_id')
        ->get();
0


source


As stated, setting strict mode to false can lead to security bugs and I am trying to set sql_mode to empty before queries that require it. Please note that this is a TEMPORARY change, when your connection is closed (as requested by laravel), you will be assigned the sql_mode = only_full_group_by value (or higher).

DB :: statement ("SET sql_mode = ''");

Hooray, happy coding ...

PS: this is not a Laravel bug, if you try to execute this query directly on your DB you will encounter the same result. This workaround works in both mysql and the first statement, and again will be a temporary session change rather than a permanent one.

0


source


I solved it by setting the modes in config / database.php file.

Set the modes as follows:

'modes'  => [
                'STRICT_TRANS_TABLES',
                'NO_ZERO_IN_DATE',
                'NO_ZERO_DATE',
                'ERROR_FOR_DIVISION_BY_ZERO',
                'NO_ENGINE_SUBSTITUTION',
            ]

for MySQL driver

'mysql' => [
        'driver' => 'mysql',
        'host' => env('DB_HOST', 'localhost'),
        'port' => env('DB_PORT', '3306'),
        'database' => env('DB_DATABASE', 'forge'),
        'username' => env('DB_USERNAME', 'forge'),
        'password' => env('DB_PASSWORD', ''),
        'charset' => 'utf8',
        'collation' => 'utf8_unicode_ci',
        'prefix' => '',
        'strict' => true,
        'engine' => null,
        'modes'  => [
            'ONLY_FULL_GROUP_BY',
            'STRICT_TRANS_TABLES',
            'NO_ZERO_IN_DATE',
            'NO_ZERO_DATE',
            'ERROR_FOR_DIVISION_BY_ZERO',
            'NO_ENGINE_SUBSTITUTION',
        ]
    ],
0


source






More articles:


All Articles