Eloquent Groupby Make "Sqlstate[42000]" with Valid SQL Query in Laravel 5.3

Eloquent groupBy make SQLSTATE[42000] with valid SQL query in Laravel 5.3

Faced same problem with laravel 5.3
They are trying to enforce strict query writing came with mysql-5.7

However to disabled this just go to config/database.php and change strict flag

'mysql' => [
            .
            .
            .
            'strict' => false,
            //'strict' => true,
            .
            .
        ],

Hope this will solve your problem too.

PS - For details on strict query writing refer to @Shadow's answer

Laravel5: SQLSTATE[42000]: Syntax error or access violation: 1055

You need a different approach. Get all sells and items from the database and then group them:

$buyLists = Sell::join('items', 'sells.item_id', '=', 'items.item_id')
    ->select('sells.*', 'items.item_title')
    ->whereDate('sells.created_at', $timeCheck)
    ->get();
$groupedBuyLists = $buyLists->groupBy('ticket_number');
foreach($groupedBuyLists as $ticketNumber => $sells) {
    $title = implode(', ', $sells->pluck('item_title')->all());
    foreach($sells as $sell) {
        // $sell->status
    } 
}

For items you should use a relationship and eager loading instead of a JOIN.

Laravel Eloquent GroupBy throwing SQL error

Change your query to a query builder query

DB::table('crawl_results')->where('user_id', Auth::id())->where('website_id', $scanID)->where('item_state', '!=' , 'OK')->groupby('destination_url')->paginate(30);

It should work fine.

In documentation it is mentioned that GROUP BY can not be executed efficiently in pagination

Currently, pagination operations that use a groupBy statement cannot
be executed efficiently by Laravel. If you need to use a groupBy with
a paginated result set, it is recommended that you query the database
and create a paginator manually.

Laravel error when get data from mySQL using groupBy

This is caused by MySQL's strict mode. Change strict to false in your config/database.php file.

When you open the file, in the mysql array, set strict => false. After you disable MySQL's strict mode it shouldn't show the error anymore.

---

Related Topics