在 Cakephp 4 中使用 contains()、order() 和 limit() 进行查询时出现问题

Question

我有以下两个关联：

AgendaDates hasOne ImageDescriptive

AgendaDates hasMany Photos

当我执行此查询时：

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

出现以下错误：

SQLSTATE[42000]：语法错误或访问冲突：1055 ORDER BY 子句的表达式 #1 不在 GROUP BY 子句中并且包含非聚合列“brigitte Patient.AgendaDates.date”在功能上不依赖于 GROUP BY 子句中的列；这与 sql_mode=only_full_group_by 不兼容

返回错误的 SQL 查询是：

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'

奇怪的是，当我做同样的事情但使用照片时（通过 hasMany 而不是 < 链接到 AgendaDates） code>hasOne) ：

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

我没有错误并且查询看起来像这样：

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'

我真的不明白为什么我的 cakephp 查询在以下情况下会抛出错误hasOne 关联，但在 hasMany 关联的情况下则不然？

Answer 1

严格分组

only_full_group_by 模式要求 SELECT、HAVING 和 ORDER BY 中的所有字段必须是功能相关的 (列a 唯一确定列b），或聚合。

分组时，组中列的所有值不一定都相等，想象一下 Articles hasMany Comments 关系，如果您加入 Comments 并按 Articles 分组.id，Articles 的所有列都可以通过 id 主键列确定，而 Comments 的列则不能，有可能有多个不同的每个 Article.id 的 Comments 列值，如下所示：

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

如果你有这样的结果，那么 MySQL 将不知道选择两个不同的 Comments.created 值中的哪一个当要求按组排序时，从该组中删除，因此发出可能产生此类结果的查询将引发错误。

当严格分组模式被禁用时，MySQL将被允许从组中基本上随机地选择一个值，这使得结果不可预测，这可能是一个问题。使用聚合可以解决这个问题，例如 MIN(Comments.created) 使用组中的最小日期值。

您可以在文档中以及通常在互联网上找到有关该主题的更多信息：

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

hasMany 与 hasOne

您的两个查询生成不同的 SQL，hasMany 关联始终在单独的查询中检索，那就是那里您正在调用 translations 查找器，这是一个单独的查询，其中没有发生连接、排序和限制，因此翻译分组不会导致任何问题，因为只有一个字段用于分组涉及。

hasOne 关联默认使用联接，因此是单个查询，因此您在该单个查询上调用 translations 查找器，即您要进行排序的查询和限制，这就是翻译分组与引用组中字段的顺序之间的冲突的根源。

tl;dr

通过对翻译行为使用 select 策略应该可以避免此问题，然后使用查询的主键而不是应用分组的子查询来获取翻译

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

： ImageDescriptive 关联的 INNER 联接也可能解决该问题（即查询可能生成的多个 null 值是函数依赖问题的根源），但它当然会改变涉及 null 值时查询记录的方式。