Group By with CodeIgniter [closed]

Question

I use CodeIgniter for my project and this is my code in model

public function group_all_ville(){  
        $this->db->select('*');
        $this->db->from('departement');
        $this->db->join('villes', 'villes.num_dept = departement.num_dept');
        $this->db->group_by('nom_dept'); 
        $query = $this->db->get(); 
        return $query->result();
    }

and this is error after execution

A Database Error Occurred

Error Number: 1055

Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'pneu.departement.id_dept' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

SELECT * FROM `departement` JOIN `villes` ON `villes`.`num_dept` = `departement`.`num_dept` GROUP BY `nom_dept`

Filename: C:/wamp64/www/pneu/system/database/DB_driver.php

Line Number: 691

I try different method but nothing result. Thanks

Answer 1

use order_by clause as well

$this->db->group_by('nom_dept'); 
$this->db->order_by('nom_dept', 'asc');  # or desc

---

FYI : Setting SQL mode and Session set is not fix the actual error.

Examples (Better not to Do)

1. https://stackoverflow.com/a/35729681/4595675

Answer 2

This solution worked for me:

$this->db->select('d.nom_dept');
$this->db->from('departement AS d, villes as v');
$this->db->where('v.num_dept = d.num_dept');
$this->db->group_by('d.nom_dept');

Refer to 12.20.3 MySQL Handling of GROUP BY for more information.

Answer 3

Hi I encountered this problem also when the system migrated to mysql8.0. Since, there are many queries which involved grouping. I used the code without changes on mysql config file. If you don't want to disable the ONLY_FULL_GROUP_BY you can review your query individually. SO explain it here

$this->db->query("SET sql_mode=(SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));");