Why does groupby query create syntax error

Question

i have problem with GroupBy in Laravel. Error that is writen is:

SQLSTATE[42000]: Syntax error or access violation: 1055 'smf.serije_epizode.id' isn't in GROUP BY (SQL: select * from `serije_epizode` where `id_serije` = 1 group by `sezona` order by `sezona` asc)

Also, fields in database are: https://i.sstatic.net/e6akE.png

And query in laravel framework is:

public function prikaz($id)
{
    $serija = DB::table('serije')
        ->where('id', $id)
        ->first();
    $epizode = DB::table('serije_epizode')
        ->where('id_serije', '=', $id)
        ->orderBy('sezona', 'asc')
        ->groupBy('sezona')
        ->get();
    return view('serije.prikaz', ['serija' => $serija, 'epizode' => $epizode]);
}

View blade:

     @foreach ($epizode as $serijaa)
Sezona: {{$serijaa->sezona}} | Epizoda: {{$serijaa->br_epizode}} - {{$serijaa->naziv}
  @endforeach

So why i just can't group by "sezona" (mean Seasons), when i have that. What am i doing wrong. Thanks. :)

every column in the select list that is not a calculated column must be declared in the group by clause. — Randy
– Randy, Commented Nov 25, 2019 at 23:10

NiGhMa · Accepted Answer · 2019-11-25 23:15:42Z

1

You request for all columns and your columns are not processed (or calculated) then you have to add them into your group by clause.

answered Nov 25, 2019 at 23:15

NiGhMa

1211 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

pr1nc3 · Accepted Answer · 2019-11-25 23:47:16Z

0

There are multiple ways to solve this issue.

The 1st and the 'best practise' is to include you column in the select query.

DB::table('serije_epizode')
        ->select('id_serije')
        ->where('id_serije', '=', $id)
        ->orderBy('sezona', 'asc')
        ->groupBy('sezona')
        ->get();

Inside the select you must have your groupBy column or generally as a rule whatever i inside select should be inside groupBy

Group by can accept multiple values so you can have something like this in your select:

select('serije_epizode','sezona','id_serije')

And in your group by you should have the same;

groupBy('serije_epizode','sezona','id_serije')

The 2nd and the "not so good practise" but have seen it in many projects is inside your database.php file you are going to find your mysql setup.

In there you are going to find a field named strict which has a value true. If you change it to false and run php artisan config:cache your group by will work.

I would strongly suggest to go for the 1st option or even better create a model and use Eloquent but that's up to you.

Another option for you might be to use distinct() or unique() to return unique values from a column instead of groupBy

edited Nov 25, 2019 at 23:47

answered Nov 25, 2019 at 23:34

pr1nc3

8,3763 gold badges27 silver badges40 bronze badges

9 Comments

PooX Over a year ago

Thanks for this advice, but still it doesn't work. I'm getting error: Syntax error or access violation: 1055 'smf.serije_epizode.id_serije' isn't in GROUP BY (SQL: select id_serije, sezona, br_epizode from serije_epizode where id_serije = 1 group by sezona order by sezona asc, br_epizode asc)

pr1nc3 Over a year ago

Judging by your error you did not use my answer. You still have 3 columns in select and 1 in group by.

PooX Over a year ago

When i add id_serije in groupby, still getting same error. Syntax error or access violation: 1055 'smf.serije_epizode.br_epizode' isn't in GROUP BY (SQL: select id_serije, sezona, br_epizode from serije_epizode where id_serije = 1 group by id_serije, sezona order by sezona asc, br_epizode asc)

PooX Over a year ago

When i add all 3 field in GroupBY, then i have same results as without groupBy. I have tv show that is (id_serije / id_show), and then number season and number episode. I want to find tv show by id then all epizodes group by seasons. That is result that i cant find.

pr1nc3 Over a year ago

I have more than 1 solutions in my answer :) give a try to the next one if you cant have this work for you. I would leave the 2nd as last though.

|

Collectives™ on Stack Overflow

Why does groupby query create syntax error

2 Answers 2

Comments

9 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

9 Comments

Your Answer

Sign up or log in

Post as a guest

Related