How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent?

Question

My laravel eloquent is 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();

When executed, there exist error like this :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'myshop.products.id' which is not functionally dependent on columns in 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 by store_id order by updated_at desc limit 4)

How can I solve it?

Answer 1

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

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

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

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

Answer 2

In folder config => database.php 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 strict is true, make it false then clear config cash by run this command in cmd

php artisan config:clear

Answer 3

I solved this problem by adding the "modes" option and setting only the modes I want to be enabled 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',
    ],
],

See more details in this tutorial

Answer 4

That's because latest versions of MySQL behave like most dbms already do regarding group by clauses; the general rule is

if you're using group by, all columns in your select must be either present in the group by or aggregated by an aggregation function (sum, count, avg and so on)

Your current query is grouping by store_id, but since you're selecting everything the rule above is not respected.

Answer 5

set

'strict' => false

in your config/database.php file. In array connections => mysql =>

in my case I'm using mysql 5.7 Laravel 5.7

Answer 6

In the .env file ADD variable: DB_STRICT=false.

And REPLACE in file from the location: config/database.php, next codes 'strict' => true ON 'strict' => (env('DB_STRICT', 'true') === 'true' ? true : false).

good luck.

Answer 7

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

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

Answer 8

You should NOT disable strict or remove ONLY_FULL_GROUP_BY. The problem is that your query is ambiguous. That may not make a difference to your output, or it could cause huge problems. It's better for you to be sure.

A great explanation can be read on Percona (summed up below).

Problem

Consider the following situation:

+----+--------------------+---------+---------------------+
| id | page_url           | user_id | ts                  |
+----+--------------------+---------+---------------------+
|  1 | /index.html        |       1 | 2019-04-17 12:21:32 |
|  2 | /index.html        |       2 | 2019-04-17 12:21:35 |
|  3 | /news.php          |       1 | 2019-04-17 12:22:11 |
|  4 | /store_offers.php  |       3 | 2019-04-17 12:22:41 |
|  5 | /store_offers.html |       2 | 2019-04-17 12:23:04 |
|  6 | /faq.html          |       1 | 2019-04-17 12:23:22 |
|  7 | /index.html        |       3 | 2019-04-17 12:32:25 |
|  8 | /news.php          |       2 | 2019-04-17 12:32:38 |
+----+--------------------+---------+---------------------+

Now we want to issue a query to calculate the most visited pages. This is probably what you're used to writing:

SELECT page_url, user_id, COUNT(*) AS visits 
FROM web_log 
GROUP BY page_url 
ORDER BY COUNT(*) DESC;

But look at the results:

+-------------------+---------+--------+
| page_url          | user_id | visits |
+-------------------+---------+--------+
| /index.html       |       1 |      3 |
| /news.php         |       1 |      2 |
| /store_offers.php |       3 |      2 |
| /faq.html         |       1 |      1 |
+-------------------+---------+--------+

The query works, but it’s not really correct. It is easily understandable that page_url is the column of the grouping function, the value we are most interested in and we want to be unique for counting. Also, the visits column is good, as it’s the counter. But what about user_id? What does this column represent?

We grouped on the page_url so the value returned for user_id is just one of the values in the group. In fact, it was not only user to visit the index.html, as users 2 and 3 visited the page. So what should we make of that value? Is it the first visitor? Is it the last one?

We don’t know the right answer! The user_id column’s value is a random item of the group!

Solution

You need to consider if you need the values not used in the groupBy(). If not, then just use a select() to explicitly name the column you need.

If you do need that column not used in the groupBy(), use an aggregate function (like ANY_VALUE() or GROUP_CONCAT() or MAX()) as part of a Laravel selectRaw query. Then you can be sure that your query is giving you what you expect.

So in the above example, you could do:

SELECT page_url, ANY_VALUE(user_id), COUNT(*) AS visits 
FROM web_log 
GROUP BY page_url 
ORDER BY COUNT(*) DESC;

Or in Laravel:

WebLog::selectRaw('page_url', 'ANY_VALUE(user_id)', 'COUNT(*) AS visits')
->groupBy('page_url')
->orderBy('visits')
->get();

Answer 9

What I did as a workaround and to prevent further security issues I make it happen like this:

 public function getLatestModels (){
        \DB::statement("SET SQL_MODE=''");
        $latestInserted = Glasses::with('store.deliveryType','glassesHasTags','glassesHasColors','glassesHasSizes','glassesHasImages','glassesBrand','glassesMaterial')->whereRaw("store_id in (select distinct store_id from glasses)")->groupBy('store_id')->orderBy('created_at')->take(8)->get();
        \DB::statement("SET SQL_MODE=only_full_group_by");

        return $latestInserted;
    }

this is a kind of combination of other answers. Also if you are using "use Illuminate\Support\Facades\DB;" you don't need backslashes in those DB statements above.

The only disadvantage here is that we are making three calls to db :(

p.s. As I see @Felipe Pena answer I guess the second statement is unnecessary

Answer 10

 #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(''); just before eloquent/query

Answer 11

As said, set strict mode to false may give security bugs, what i am doing is to set sql_mode to empty before queries that require it. Note that it is a TEMPORARY change, once your connection is close (by laravel request) you will be set to original sql_mode=only_full_group_by (or beyond).

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

Cheers, happy coding...

ps.: its not laravel fault, if you try to execute this query directly on your DB you will face same result. This work around works in mysql as well as first statement and again, will be a temporary session change, not permanent.

Answer 12

Check the query:

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 by store_id and fetching all columns in the result set which is not allowed. To solve it either select store_id or aggregate function on it or change the system variable sql_mode=only_full_group_by to SET sql_mode = ''.

Reference

To set this in Laravel try this:

'strict' => false

in your config/database.php file. In array connections => mysql =>

Answer 13

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

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