I have written this query with pagination in it
$items = Item::select('items.*', 'sub_category_name', 'category_name', 'sub_category_slug', 'category_slug')
->join('sub_categories AS sc', 'sc.sc_id', 'items.sub_category_id')
->join('categories AS c', 'c.category_id', 'sc.category_id')
->where('items.is_active', '=', 1)
->where('sc.is_active', '=', 1)
->where('c.is_active', '=', 1)
->where('sc.sc_id', '=', $sub_category_id)
->paginate(1);
But it says
Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
But when I add ->groupBy('item_id');
it says
Syntax error or access violation: 1055 'books.items.item_name' isn't in GROUP BY
But when I do item_name in groupBy clause it says to groupBy the next column. Why?
When you use aggregate functions like MIN(),MAX(),COUNT() AVG()
you have to use Group BY
But in latest MYSQL
u have to use all the columns in select as a group By too.
In your config/database.php
turn off the strict mode.
'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' => false,
'engine' => null,
],
'strict' => false
after that you can use group by on a single column too.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With