Bug: Method first() in model is forcing group by

[WillRy]

PHP Version

8.1

CodeIgniter4 Version

4.2.0

CodeIgniter4 Installation Method

Composer (using codeigniter4/appstarter)

Which operating systems have you tested for this bug?

Linux

Which server did you use?

apache

Database

MySQL 8

What happened?

When making a query that has several "joins", bringing the fields from more than one table, the "first" method forces a group by in the primary key, in the CodeIgniter\Model class, in the doFirst() method, line 240:

$builder->groupBy($this->table . '.' . $this->primaryKey);

This generates a bug in almost all MySQL 8 installations, due to the installation coming with "only_full_group_by" mode enabled.

I believe that they should not force a group by unnecessarily, because this increases the cost of the query in some cases and also causes an error: only_full_group_by - mysqli_sql_exception

Here's an example of the error:

<?php
$attributes = [
    'products.*',

    'categories.id AS category_id',
    'categories.name AS category_name',
];

$ordem = $this
    ->select($attributes)
    ->join('categories_products', 'categories_products.product_id = products.id', "LEFT")
    ->join('categories', 'categories.id = categories_products.id', "LEFT")
    ->where('products.id', 1)
    ->withDeleted()
    ->first();

The error happens, because the only_full_group_by mode requires that all select fields are in group by, to avoid problems and bugs in aggregation. The first() method forces a group by on only one field, which is the primary key.

Thank you very much in advance

Steps to Reproduce

Execute a query with multiple joins, bringing data from more than one table in select, with the first() method.

IMPORTANT: Use MYSQL 8 in newer versions, with only_full_group_by enabled

<?php
$attributes = [
    'products.*',

    'categories.id AS category_id',
    'categories.name AS category_name',
];

$ordem = $this
    ->select($attributes)
    ->join('categories_products', 'categories_products.product_id = products.id', "LEFT")
    ->join('categories', 'categories.id = categories_products.id', "LEFT")
    ->where('products.id', 1)
    ->withDeleted()
    ->first();

Expected Output

Not having only_full_group_by errors and not making an unnecessary group by

The query that was generated was:

select
    products.*,
    categories.id AS category_id,
    categories.name AS category_name
FROM 
products
left join categories_products on categories_products.product_id = products.id
left join categories on categories.id = categories_products.category_id
where product.id = 1
group by product.id;

The query that should have been generated(without group by)

select
    products.*,
    categories.id AS category_id,
    categories.name AS category_name
FROM 
products
left join categories_products on categories_products.product_id = products.id
left join categories on categories.id = categories_products.category_id
where product.id = 1;

Anything else?

No response

[kenjis]

Ref #2907

[kenjis]

This does not seem to be a bug.

The CodeIgniter’s Model provides convenience features and additional functionality that people commonly use to make working with a single table in your database more convenient.
https://codeigniter4.github.io/CodeIgniter4/models/model.html

[WillRy]

This does not seem to be a bug.

The CodeIgniter’s Model provides convenience features and additional functionality that people commonly use to make working with a single table in your database more convenient.
https://codeigniter4.github.io/CodeIgniter4/models/model.html

The idea of working with a single table doesn't seem to be a good idea, because there is a way to "join" the model and also the problem doesn't happen in the findAll() method, only in the first()

If you keep this behavior in first(), mysql installations with only_full_group_by will have problems.

To solve this problem, it would be enough to not force the group by in the first() method

[kenjis]

@WillRy if you can resolve this issue well, please send a PR.
But just removing the group by is unacceptable, because the group by was added out of necessity.

See https://github.com/codeigniter4/CodeIgniter4/blob/develop/contributing/README.md

[sclubricants]

only_full_group_by mode is annoying. I'd recommend disabling it if you know what your doing.

I've been wondering if/when/how this might have an impact on CI.

If you are going to use only_full_group_by mode then you should be defining the group by statements explicitly. That is really the reasoning behind it all. This way your defining the meaning of all the columns used.

[sclubricants]

Also understand that model is a model. A query like yours is creating many versions of the same model rather than many different models.

Use builder to run general queries.