[FIXED] Problem with a query using contain(), order() and limit() in Cakephp 4

Issue

I have the two following associations :

AgendaDates hasOne ImageDescriptive

AgendaDates hasMany Photos

When I do this query :

class="lang-php prettyprint-override">$dates = $this->AgendaDates
                        ->find()
                        ->order(['AgendaDates.date' => 'ASC'])
                        ->contain(['ImageDescriptive' => function ($q) { // AgendaDates hasOne ImageDescriptive
                            return $q->find('translations');
                        }])
                        ->limit(2);

I have the following error :

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'brigittepatient.AgendaDates.date' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

The SQL query that returns an error is :

SELECT 
  FichiersI18n.id AS FichiersI18n__id, 
  FichiersI18n.locale AS FichiersI18n__locale, 
  FichiersI18n.model AS FichiersI18n__model, 
  FichiersI18n.foreign_key AS FichiersI18n__foreign_key, 
  FichiersI18n.field AS FichiersI18n__field, 
  FichiersI18n.content AS FichiersI18n__content 
FROM 
  fichiers_i18n FichiersI18n 
  INNER JOIN (
    SELECT 
      (ImageDescriptive.id) 
    FROM 
      agenda_dates AgendaDates 
      LEFT JOIN fichiers ImageDescriptive ON (
        ImageDescriptive.model = 'AgendaDates' 
        AND ImageDescriptive.field = 'image_descriptive' 
        AND AgendaDates.id = ImageDescriptive.foreign_key
      ) 
    GROUP BY 
      ImageDescriptive.id 
    ORDER BY 
      AgendaDates.date ASC 
    LIMIT 
      2
  ) ImageDescriptive ON FichiersI18n.foreign_key = ImageDescriptive.id 
WHERE 
  FichiersI18n.model = 'Fichiers'

The strange thing is that when I do the same thing but with Photos (linked to AgendaDates by hasMany instead of hasOne) :

$dates = $this->AgendaDates
                        ->find()
                        ->order(['AgendaDates.date' => 'ASC'])
                        ->contain(['Photos' => function ($q) { // AgendaDates hasMany Photos
                            return $q->find('translations');
                        }])
                        ->limit(2);

I have no error and the query looks like that :

SELECT 
  FichiersI18n.id AS FichiersI18n__id, 
  FichiersI18n.locale AS FichiersI18n__locale, 
  FichiersI18n.model AS FichiersI18n__model, 
  FichiersI18n.foreign_key AS FichiersI18n__foreign_key, 
  FichiersI18n.field AS FichiersI18n__field, 
  FichiersI18n.content AS FichiersI18n__content 
FROM 
  fichiers_i18n FichiersI18n 
  INNER JOIN (
    SELECT 
      (Photos.id) 
    FROM 
      fichiers Photos 
    WHERE 
      (
        Photos.model = 'AgendaDates' 
        AND Photos.field = 'photos' 
        AND Photos.foreign_key in (5, 3)
      ) 
    GROUP BY 
      Photos.id
  ) Photos ON FichiersI18n.foreign_key = Photos.id 
WHERE 
  FichiersI18n.model = 'Fichiers'

I really don't understand why my cakephp query throws an error in the case of hasOne association but not in the case of hasMany association ?

Solution

Strict Grouping

The only_full_group_by mode requires that all fields in SELECT, HAVING and ORDER BY must be either functionally dependent (column a uniquely determines column b), or aggregates.

When grouping, not all values for a column in a group will necessarily be equal, imagine an Articles hasMany Comments relation, if you join in Comments and group by Articles.id, all columns of Articles can be determined by the id primary key column, the columns of Comments however cannot, there could be multiple different Comments column values per Article.id, something like this:

Articles.id	Comments.created
1	2022-01-01
1	2022-01-02
2	2022-02-01
2	2022-02-02

If you had such results, then MySQL wouldn't know which of the two different Comments.created values to pick from the group when asked to order by it, hence issuing a query that could produce such a result will throw an error.

When strict group by mode is disabled, then MySQL would be allowed to pick a value from the group basically at random, which makes the results unpredictable, which can be a problem. Using an aggegrate can fix that, for example MIN(Comments.created) to use the smallest date value from the groups.

You can find more on that topic in the docs, and generally all over the internet:

• https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html
• https://dev.mysql.com/doc/refman/5.7/en/group-by-functional-dependence.html

hasMany vs hasOne

Your two queries produce different SQL, hasMany associations are always retrieved in a separate query, that is where you're calling the translations finder on, a separate query where no joining, ordering and limiting is happening, hence the grouping for the translations will not cause any problems since only the one field that is used for grouping is involved.

hasOne association are using joins by default, so a single query, hence here you're calling the translations finder on that single query, the one where you're doing the sorting and limiting, and that's where the clash between the grouping for the translations and the ordering that references a field from the group stems from.

tl;dr

It should be possible to avoid this problem by using the select strategy for the translate behavior, which will then obtain the translations using the queried primary keys instead of a subquery where grouping is applied:

$this->addBehavior('Translate', [
    'strategy' => 'select',
    // ...
]);

Using an INNER join for the ImageDescriptive association might possibly also fix the problem (that is in case the possible multiple null values that the query could produce are the source of the functional dependency problem), but it would of course change how records are queried when null values are involved.

Answered By - ndm

Answer Checked By - Robin (PHPFixing Admin)