sql_mode incompatible codeigniter 4

Question

I have a query, it works fine on the local wamp server, but uploading to mye webserver gives me an error. Have looked at similar solutions but cant understand it enough to adapt it to my query.

The error:

Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'intranett3.skift.start' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I have 2 tables, one main with assignement and a table that countains shifts to that assignement

ex table vakter (assignement is named oppdrag in my table and start/stop is named start/slutt)

vakt_id	assignement	...
1	Mission 1	...

ex table skift

skift_id	vakt_id	start	stop	...
1	1	2021-06-21 17:00:00	2021-06-21 18:00	...
2	1	2021-06-21 18:00:00	2021-06-21 21:00	...

I want to show

Assignement	start	stop
Mission 1	2021-06-21 17:00:00	2021-06-21 21:00

Where start date is the earliest time in skift where vakt_id is 1 and stop date is the latest time in the same rows.

The tables

CREATE TABLE IF NOT EXISTS `vakter` (
  `vakt_id` int(5) NOT NULL AUTO_INCREMENT,
  `status` enum('Aktiv','Utsatt','Avlyst','Ubekreftet') NOT NULL,
  `lag_id` int(5) NOT NULL,
  `type_id` int(5) NOT NULL,
  `sted_id` int(5) NOT NULL,
  `start` datetime DEFAULT NULL,  #this is not populated
  `slutt` datetime DEFAULT NULL,  #this is not populated
  `oppdrag` varchar(255) NOT NULL,
  `info` text NOT NULL,
  `kunde_id` int(5) NOT NULL,
  `kunderef` varchar(255) NOT NULL,
  `rapportok` tinyint(1) NOT NULL,
  `created_by` int(5) NOT NULL,
  `created` datetime NOT NULL,
  PRIMARY KEY (`vakt_id`)
) ENGINE=InnoDB AUTO_INCREMENT=1568 DEFAULT CHARSET=latin1;

and table 2

CREATE TABLE IF NOT EXISTS `skift` (
  `skift_id` int(11) NOT NULL AUTO_INCREMENT,
  `vakt_id` int(5) NOT NULL,
  `start` datetime NOT NULL,
  `slutt` datetime NOT NULL,
  `mannskap` int(5) NOT NULL,
  `hospitant` int(5) NOT NULL,
  `bil` varchar(255) NOT NULL,
  `ressursgruppe` int(5) NOT NULL,
  PRIMARY KEY (`skift_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2223 DEFAULT CHARSET=latin1;

My Query

public function paginatevakter(int $nb_page, int $lagID) {
        return $this->select('vakter.oppdrag, vakter.vakt_id')
        ->selectMin('skift.start', 'sstart')
        ->selectMax('skift.slutt', 'sslutt')
        ->join('skift', 'skift.vakt_id = vakter.vakt_id', 'LEFT')
        ->where('skift.start > now()')
        ->where('lag_id', $lagID)
        ->selectCount('skift.vakt_id', 'ant_skift')
        ->orderBy('skift.start')
        ->groupBy('vakter.vakt_id')
        ->paginate($nb_page, 'show');
    }

Have tried different values in group by.

PS cant use start /stop(slutt) in table one, because its an import from an older db where those werent used...

`->groupBy('vakter.oppdrag, vakter.vakt_id')` + `->orderBy('sstart')` (or `->orderBy('sslutt')`) — Akina, Jun 16 '21 at 06:46

score 0 · Answer 1 · answered Jun 16 '21 at 07:03

You want to pack several rows together when they have the same vakt_id:

->groupBy('vakter.vakt_id')

... and display a calculated value (the minimum value for start among all those rows):

->selectMin('skift.start', 'sstart')

But then you ask to sort the results of this by the original value:

->orderBy('skift.start')

This does not make sense from a logical standpoint. The sql_mode=only_full_group_by in one of your servers is there to prevent MySQL from ignoring this and just producing a potentially wrong outcome.

Well, that is why i asked the question, I could not see this myself, but when you explained it like this, it makes sense. Learning by doing, ;) Thank you. Used the suggestion as explained in comment, and this solved it :) — Bengt, Jun 16 '21 at 07:34

sql_mode incompatible codeigniter 4

1 Answers1