Group by error with PostgreSQL

[corozcop]

Error Number:
ERROR: column "pay_paymanager.paymentid" must appear in the GROUP BY clause or be used in an aggregate function LINE 1: SELECT * ^

SELECT * FROM "pay_paymanager" WHERE payperiod::DATE between '2016-08-01'::DATE and '2016-08-31'::DATE GROUP BY "customername"

Filename: /var/www/frontend/libraries/Datatables.php
Line Number: 457

[gastongr]

Something similar is happening with MySQL 5.7

MySQL 5.7.5 and up implements detection of functional dependence. If the ONLY_FULL_GROUP_BY SQL mode is enabled (which it is by default), MySQL rejects queries for which the select list, HAVING condition, or ORDER BY list refer to nonaggregated columns that are neither named in the GROUP BY clause nor are functionally dependent on them. (Before 5.7.5, MySQL does not detect functional dependency and ONLY_FULL_GROUP_BY is not enabled by default.)
https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html

This line
$query = $this->ci->db->get($this->table, null, null, false);
Is doing a
SELECT * FROM ...
But there might also be a group by on specific columns, hence when this happens it fails.

A quick fix: specify column names in get_total_results(), selecting only the columns used on the GROUP BY

Around line 443 replace

foreach($this->group_by as $val)
  $this->ci->db->group_by($val);

with:

foreach($this->group_by as $val) {
    $this->ci->db->group_by($val);
    $this->ci->db->select($val);
}

This will fix the query used to count the result,s but you also have to write your own queries with that restriction in mind.