Reputation: 1772
I've been trying to solve this, my first problem was this and its kind of working as expected.
The problem is that I can't select more columns than the ones in groupBy, so I've found a solution: DB::raw('created_at as created_at')
But it's returning me this:
SQLSTATE[42000]: Syntax error or access violation: 1055 created_at' isn't in GROUP BY
Ad if I add "create_at" groupBy(DB::raw('create_at'))
and "concept" to groupBy, then they are grouped that is what I want to try to avoid.
My code is:
@forelse($transaction->where('status', '!=', 'ok')
->select('user_id', 'option_id', 'status', 'concept', DB::raw('created_at as created_at'), DB::raw('concept as concept'))
->groupBy('user_id')
->groupBy('option_id')
->groupBy('status')
->get() as $transaction)
{{$transaction->concept}}
{{$transaction->created_at}}
Thank you :)
Upvotes: 0
Views: 851
Reputation: 1772
Well, thanks to @noufalcep and @Rob Fonseca Rob, I came with this solution, I think this happens in Laravel after versión 5.4 where strict is set to true.
So instead of disabling it completely I did this:
'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, #####I COMMENTED THIS####
'modes' => [
//'ONLY_FULL_GROUP_BY', #####AND THIS####
'STRICT_TRANS_TABLES',
'NO_ZERO_IN_DATE',
'NO_ZERO_DATE',
'ERROR_FOR_DIVISION_BY_ZERO',
'NO_AUTO_CREATE_USER',
'NO_ENGINE_SUBSTITUTION',
],
'engine' => null,
],
By the way, MAX MIN didn't work for me, it was still requesting to add it into groupBy and asking for an array
I hope this will help the next one.
Happy coding
Upvotes: -1
Reputation: 3534
Mysql strict mode is enabled by default. Disable strict mode, it will solve your issue.
In config/database.php under 'mysql'
'strict' => true,
to false.
'strict' => false,
Upvotes: 2
Reputation: 3849
Every column in a SELECT statement must also be in the GROUP BY clause.
There is a exception by setting the only_full_group_by mode to false in MySQL 5.7.5 or higher, but the assumption is the extra columns have the same value for all rows https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
You can also accomplish this by using tricks to get a MAX() or MIN() for the non-grouped columns but you can not get the actual values of those columns for each row unless they are added to the GROUP BY statement
Upvotes: 2