only_full_group_by - mysqli_sql_exception #1055 - MySql 5.7.24

[perspolise]

I work with CI version 4.0.2 and MySql 5.7.24:

    public function getProducts(int $id = null, $manufacturer = null, $size = null)
    {
        $builder = $this->db->table('product_categories pc');
        $builder->select('p.id, p.name, p.slug, p.manufacturer_id, p.size size, pm.name manufacturer_name, pp.old_price , pp.new_price, pp.created_at');
        $builder->join('products p', 'p.id = pc.product_id');
        $builder->join('product_manufacturers pm', 'p.manufacturer_id = pm.id');
        $builder->join('product_prices pp', 'p.id = pp.product_id');
        $builder->where('pc.category_id', $id);
        $builder->whereIn('p.manufacturer_id', $manufacturer);
        $builder->whereIn('p.size', $size);
        $builder->groupBy('pp.product_id');  //problem is here
        $query = $builder->get()->getResult();
        return $query;
    }

error is:

Expression #7 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'ci4.pp.old_price' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

I know, I can fix this problem with disable sql_mode=only_full_group_by but, I think this method isn't good.

[michalsn]

I don't really see an issue here. How would you like to deal with it?

I'm closing this since it's not a bug, but feel free to continue the conversation.

[perspolise]

@michalsn @lonnieezell

[michalsn]

@perspolise This is a MySQL error (not CodeIgniter) and you already pointed out how to fix it - you should update your configuration. Could you elaborate on what is your idea to handle it better?

[perspolise]

@michalsn : In MySql 5.7.24 sql_mode=only_full_group_by is enable in default configuration and for fix this problem we need edit configuration(disable it). So in sharing hosting is bad idea.

[michalsn]

@perspolise There are no changes since CI3 when it comes to handling this and personally I don't feel that adding some sort of support for this right now would be a good idea. If you really can't work around it I would suggest using post_controller_constructor event to change sql_mode for a given session, something like this:

Events::on('post_controller_constructor', function() {
	db_connect()->simpleQuery("SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY',''))");
});