CODEWITHSUNDEEP
phpmysqlcodeignitercodeigniter-3
P. Nick
P. Nick

Reputation: 991

CodeIgniter 3 - nonaggregated column group by

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.

Upvotes: 1

Views: 4639

Answers (2)

Laksh Sakure
Laksh Sakure

Reputation: 51

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

Upvotes: 5

Jeremy Harris
Jeremy Harris

Reputation: 24549

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:

  1. 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

  2. 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

Upvotes: 1

Related Questions