A.* isn't in GROUP BY with left join on laravel query builder

Question

   $search_alls=
    DB::table('a16s as A')
    ->select('A.id')
    // ->select('A.*')
    ->addSelect(DB::raw('SUM(CASE WHEN B.approve = 1 ELSE 0 END) as Yshow'))
    ->leftjoin('a16s_likes as B', function($join) {
        $join->on('A.id', '=', 'B.p_id');
        })
    ->groupBy('A.id')
    ->get();

when I use above select('A.id') is work well.

But when I use select('A.*') to select all A cloumn I got the error

SQLSTATE[42000]: Syntax error or access violation: 1055 'employee.A.name' isn't in GROUP BY

PS:employee is my DB name The column on A table is

id name ....
1  john
2  mary
3  susan

How can I select all column by the leftjoin? the column A.id is one to many relationship to the B.p_id column.

Is `id` a primary key column? If not, then selecting `A.*` is not valid. — Tim Biegeleisen, Jun 20 '18 at 05:53
check this question https://stackoverflow.com/questions/25800411/mysql-isnt-in-group-by — afsal c, Jun 20 '18 at 06:01
Possible duplicate of [Error related to only\_full\_group\_by when executing a query in MySql](https://stackoverflow.com/questions/34115174/error-related-to-only-full-group-by-when-executing-a-query-in-mysql) — philipxy, Aug 13 '19 at 07:00
Does this answer your question? [Group by not working - Laravel](https://stackoverflow.com/questions/41571271/group-by-not-working-laravel) — miken32, Jan 30 '20 at 22:19

score 5 · Accepted Answer · answered Jun 20 '18 at 06:57

To fix this issue you need to specify required columns in select list and group by clause

$search_alls=DB::table('a16s as A')
                ->select('A.id','A.name')
                ->addSelect(DB::raw('SUM(CASE WHEN B.approve = 1 ELSE 0 END) as Yshow'))
                ->leftjoin('a16s_likes as B', function($join) {
                    $join->on('A.id', '=', 'B.p_id');
                })
                ->groupBy('A.id')
                ->groupBy('A.name');
    ->get();

As per newer release mysql 5.7 does not permit queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are not named in the GROUP BY clause

12.19.3 MySQL Handling of GROUP BY

As per docs

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default

It's true. I cannot use * and must to grouBy(...) all column. It works~thanks a lot! But not any solution to use * as it is convenient? — robspin, Jun 20 '18 at 07:15
@robspin To use `*` you need to perform aggregation of a16s_likes in seperate sub clause and then join this clause with your table — M Khalid Junaid, Jun 20 '18 at 07:23

score 2 · Answer 2 · answered Jun 20 '18 at 05:52

2

use select('A.*') to select all columns

 $search_alls=
    DB::table('a16s as A') 
    ->select('A.*')
    ->addSelect(DB::raw('SUM(CASE WHEN B.approve = 1 ELSE 0 END) as Yshow'))
    ->leftjoin('a16s_likes as B', function($join) {
        $join->on('A.id', '=', 'B.p_id');
        })
    ->groupBy('A.id')
    ->get();

answered Jun 20 '18 at 05:52

Saurabh Mistry

12,833
5
50
71

How does this fix the OP? – Tim Biegeleisen Jun 20 '18 at 05:53
solved my problem – Pejman Kheyri Dec 03 '21 at 19:00

score 2 · Answer 3 · answered Aug 13 '19 at 06:38

I saw your issue in detail. and I've faced same issue. in database.php, there are below setting

'mysql' => [
            'driver' => 'mysql',
            'url' => env('DATABASE_URL'),
            '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' => 'utf8mb4',
            'collation' => 'utf8mb4_unicode_ci',
            'prefix' => '',
            'prefix_indexes' => true,
            'strict' => false,
            'engine' => null,
            'options' => extension_loaded('pdo_mysql') ? array_filter([
                PDO::MYSQL_ATTR_SSL_CA => env('MYSQL_ATTR_SSL_CA'),
            ]) : [],
        ],

When I set strict property as false, it was working, and i've solved issue.

score 1 · Answer 4 · answered Jun 20 '18 at 06:48

1

You have to turn off sctrict mode in Laravel application.

go to config/database.php

under the MySQL set

strict => false

and it will start working.

Hope this helps.

answered Jun 20 '18 at 06:48

FULL STACK DEV

15,207
5
46
66

A.* isn't in GROUP BY with left join on laravel query builder

4 Answers4

Linked

Related