CODEWITHSUNDEEP
sqllaravel
PooX
PooX

Reputation: 81

Why does groupby query create syntax error

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. :)

Upvotes: 0

Views: 190

Answers (2)

pr1nc3
pr1nc3

Reputation: 8338

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

Upvotes: 0

NiGhMa
NiGhMa

Reputation: 121

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

Upvotes: 1

Related Questions