Group By with CodeIgniter

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

It seems rather odd that you are using a `GROUP BY` clause and `SELECT *`. GROUP BY is most appropriate when you want to use aggregate function (e.g. `SUM()`, `COUNT()`, etc) in your `SELECT`. This question shows code and an error message, but it fails to include table schemas, sample input, and the desired output. The absence of a [mcve] makes the question Unclear. I have to assume that you are seeking unique `nom_debt` values, but if that is the case, there are better ways to isolate `DISTINCT` rows. — mickmackusa, Dec 06 '21 at 21:13
...actually, based on the OP's self-answer, this looks like the appropriate dupe: https://stackoverflow.com/q/656622/2943403 — mickmackusa, Dec 06 '21 at 23:20

score 11 · Accepted Answer · edited May 23 '17 at 11:46

11

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)

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

edited May 23 '17 at 11:46

Community

1
1

answered Jan 04 '17 at 20:59

Abdulla Nilam

36,589
17
64
85

score 3 · Answer 2 · edited Oct 11 '21 at 15:47

3

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.

edited Oct 11 '21 at 15:47

dbc

104,963
20
228
340

answered Jan 05 '17 at 07:29

Tojo

247
2
5
17

Christian Igay · Answer 3 · 2021-08-28T06:26:39.667

0

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', ''));");

edited Aug 28 '21 at 06:26

answered Aug 27 '21 at 02:41

Christian Igay

25
2
10

1

Please make more of an attempt to explain how your snippet works and why researchers should use this versus other techniques. Please be more generous with your insights. – mickmackusa Aug 27 '21 at 02:50
Disabling isn't a good way to resolve the problem. See [Why should not disable ONLY_FULL_GROUP_B](https://stackoverflow.com/questions/64824498/why-should-not-disable-only-full-group-by/64831540#64831540) – danblack Aug 27 '21 at 07:39

Group By with CodeIgniter

3 Answers3