Jun 11, 2021

Level 8

Why does this query in model not working ?

Hi!

Have this query thats working fine:

$gpsdata = DB::select('SELECT flightnumber, MAX(id) AS id FROM gpstrackerclubs GROUP BY flightnumber DESC;');

But if i add a new colum like :

$gpsdata = DB::select('SELECT flightnumber,timeused, MAX(id) AS id FROM gpstrackerclubs GROUP BY flightnumber DESC;');

Then it not working ?

Gives me this error

"SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'birdihuz_Easyflow.gpstrackerclubs.timeused' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: SELECT flightnumber,timeused, MAX(id) AS id FROM gpstrackerclubs GROUP BY flightnumber DESC;)",

s4muel

4 years ago

Best Answer

Level 50

you cannot select a non-aggregated column that is not part of the group by clause. you can turn off the only_full_group_by but i advise not to.

imagine you have a table like this

| ID | flightnumber | timeused |
|----|--------------|----------|
| 1  | A            | 10       |
| 2  | B            | 20       |
| 3  | B            | 30       |
| 4  | C            | 40       |

if you group by flightnumber, for the flighnumber B it picks the ID = 3 (because it it MAX() - aggregated column), but it cant decide which to pick for a timeused.

if the only_full_group_by is turned off, the result can be that what you want, but be warned that mysql is free to pick any random value from the grouped rows in this case.

i would do this in two steps, first get the IDs only and then do another select that gets all rows with those IDs

birdietorerik

4 years ago

Level 8

Hi!

That makes sence, thank you

If you can show me the SQL that do this, i be very happy :)

s4muel

4 years ago

Level 50

@birdietorerik, something like this, probably:

$gpsdata = DB::table('gpstrackerclubs')->whereIn('id', function($query) {
    $query->select('id')
        ->from('gpstrackerclubs')
        ->groupBy('flightnumber')
        ->havingRaw('MAX(id)');
})->get();

might need a little bit of tweaking, but try this as a starting point.

birdietorerik

4 years ago

Level 8

Hi!

Sorry, this give error:

"SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'birdihuz_Easyflow.gpstrackerclubs.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from gpstrackerclubs where id in (select id from gpstrackerclubs group by flightnumber having MAX(id)))", "exception": "Illuminate\Database\QueryException",

And result must give med colum timeused to

s4muel

4 years ago

Level 50

another shot:

$gpsdata = DB::table('gpstrackerclubs')
->select('id', 'flightnumber', 'timeused')
->whereIn('id', function($query) {
    $query->selectRaw('MAX(id) as id')
        ->from('gpstrackerclubs')
        ->groupBy('flightnumber');
})->get();

birdietorerik

4 years ago

Level 8

Hi!

Sorry, gives me this error:

"message": "SQLSTATE[42S22]: Column not found: 1054 Unknown column 'id,flightnumber, timeused' in 'field list' (SQL: select id,flightnumber, timeused from gpstrackerclubs where id in (select MAX(id) as id from gpstrackerclubs group by flightnumber))",

Have checked that colums have been spelled correct

birdietorerik

4 years ago

Level 8

When i use this:

$gpsdata = DB::table('gpstrackerclubs') ->select('*') ->whereIn('id', function($query) { $query->selectRaw('MAX(id) as id') ->from('gpstrackerclubs') ->groupBy('flightnumber'); })->get();

Its working fine :)

Thank you for all your help

1 like

s4muel

4 years ago

Level 50

sorry, missed some quotes, post fixed/edited

Please or to participate in this conversation.