CodeIgniter 3 - nonaggregated column group by

Question

When I try to run this query in CodeIgniter 3, it gives me this error:

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

SELECT `id`, `keyword`, `value`, `language` FROM `phrases` GROUP BY `language`

PHP:

$query = $this->db->select("id, keyword, value, language")
            ->group_by("language")
            ->get("phrases")
            ->result();

I have googled a bit but I don't quite understand the answers, mainly because the queries are not related to CI and are very complicated... How can this be fixed in codeigniter?

I do not wish to change any MySQL settings.

Have you tried with `$query = $this->db->query("SELECT id, keyword, value, language FROM phrases GROUP BY language"); var_dump($query->result());`? — Tpojka, Feb 26 '18 at 16:59
Possible duplicate of [SELECT list is not in GROUP BY clause and contains nonaggregated column .... incompatible with sql\_mode=only\_full\_group\_by](https://stackoverflow.com/questions/41887460/select-list-is-not-in-group-by-clause-and-contains-nonaggregated-column-inc) — nageen nayak, Feb 26 '19 at 05:53

score 5 · Answer 1 · edited May 02 '20 at 12:24

5

Expression #1

SELECT list is not in GROUP BY clause and contains nonaggregated column 'spd.quantity' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Just add the following line at the above of your query.

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

edited May 02 '20 at 12:24

codedge

4,754
2
22
38

answered May 02 '20 at 11:53

Laksh Sakure

51
1
2

3

Please don't post only code as answer, but also provide an explanation what your code does and how it solves the problem of the question. Answers with an explanation are usually more helpful and of better quality, and are more likely to attract upvotes. – codedge May 02 '20 at 12:23

score 1 · Answer 2 · answered Feb 26 '18 at 16:29

This is not a CodeIgniter specific problem -- it is part of the SQL standard and is built into MySQL. It exists so that it can validate your data and encourage good database design and queries. You have two options to fix it:

You can write queries the "correct" way. This was an issue with the SQL92 specification, but was later modified in the SQL99 standard to permit non-aggregates: https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
The other way, and more common response, is to modify your my.cnf file and disable the forced SQL mode of only_full_group_by. Disable ONLY_FULL_GROUP_BY

CodeIgniter 3 - nonaggregated column group by

2 Answers2