Laravel 10高级Has One Of Many关联引发SQLSTATE[42000]错误

问题场景 #

我用「高级方式」定义了Has One Of Many关联，实现逻辑和Laravel官方的高级Has One Of Many示例几乎一致。

模型代码如下：

class DocumentType extends Model
{
    use HasFactory, SoftDeletes;

    // 其他代码...

    public function currentRevision () {

       return $this->hasOne(DocumentTypeRevision::class, 'document_type_id')
                   ->ofMany (['revision_number' => 'max', 'revision_date' => 'max'], 
                              function ($qry) {
                                  $qry->where('revision_date', '<=', now()->startOfDay());
                     });
    }
}

执行以下代码时：

$revision = DocumentType::find(1)->currentRevision;

触发了SQLSTATE[42000]错误，问题根源是生成的SQL中，SELECT部分包含未出现在GROUP BY中的字段。目前临时解决方案是在database.php中关闭数据库连接的ONLY_FULL_GROUP_BY配置，但希望从根源解决问题。

生成的错误SQL #

select `document_types_revisions`.* 

from `document_types_revisions` 

inner join (

    select max(`document_types_revisions`.`id`) as `id_aggregate`, 
           `document_types_revisions`.`revision_date` as `revision_date_aggregate`, 
           `document_types_revisions`.`revision_number` as `revision_number_aggregate`, 
           `document_types_revisions`.`document_type_id` 
       
    from `document_types_revisions` 
    
    inner join (
    
        select max(`document_types_revisions`.`revision_date`) as `revision_date_aggregate`, 
               `document_types_revisions`.`revision_number` as `revision_number_aggregate`, 
               `document_types_revisions`.`document_type_id` 
               
            from `document_types_revisions` 
       
            inner join (
       
                select max(`document_types_revisions`.`revision_number`) as `revision_number_aggregate`, 
                          `document_types_revisions`.`document_type_id` 
                
                from `document_types_revisions` 
                
                where `revision_date` <= '2023-03-08 00:00:00' 
                  and `document_types_revisions`.`document_type_id` = 3 
                  and `document_types_revisions`.`document_type_id` is not null 
                  and `document_types_revisions`.`deleted_at` is null 
              
                group by `document_types_revisions`.`document_type_id`
            
        ) as `currentRevision` on `currentRevision`.`revision_number_aggregate` = `document_types_revisions`.`revision_number` 
                              and `currentRevision`.`document_type_id` = `document_types_revisions`.`document_type_id` 
                              
        where `revision_date` <= '2023-03-08 00:00:00' 
          and `document_types_revisions`.`deleted_at` is null 
          
        group by `document_types_revisions`.`document_type_id`
        
    ) as `currentRevision` on `currentRevision`.`revision_date_aggregate` = `document_types_revisions`.`revision_date` 
                          and `currentRevision`.`revision_number_aggregate` = `document_types_revisions`.`revision_number` 
                          and `currentRevision`.`document_type_id` = `document_types_revisions`.`document_type_id` 
                          
    where `revision_date` <= '2023-03-08 00:00:00' 
      and `document_types_revisions`.`deleted_at` is null 
      
    group by `document_types_revisions`.`document_type_id`
    
) as `currentRevision` on `currentRevision`.`id_aggregate` = `document_types_revisions`.`id` 
                      and `currentRevision`.`revision_date_aggregate` = `document_types_revisions`.`revision_date` 
                      and `currentRevision`.`revision_number_aggregate` = `document_types_revisions`.`revision_number` 
                      and `currentRevision`.`document_type_id` = `document_types_revisions`.`document_type_id` 
                      
where `document_types_revisions`.`document_type_id` = 3 
  and `document_types_revisions`.`document_type_id` is not null 
  and `document_types_revisions`.`deleted_at` is null 
  
limit 1;

根源解决方案 #

问题出在Laravel生成子查询时，直接选取了revision_date和revision_number字段，但这两个字段未包含在GROUP BY中，违反了ONLY_FULL_GROUP_BY的SQL规则。可以通过以下两种方式彻底修复：

方式一：改用排序逻辑筛选目标记录 #

放弃多字段聚合的写法，通过排序直接筛选出符合条件的最新版本，Laravel会自动生成符合GROUP BY规则的SQL：

public function currentRevision()
{
    return $this->hasOne(DocumentTypeRevision::class, 'document_type_id')
        ->where('revision_date', '<=', now()->startOfDay())
        ->orderByDesc('revision_number')
        ->orderByDesc('revision_date');
}

方式二：确保所有SELECT字段都被聚合或分组 #

如果要保留ofMany的多字段聚合写法，需要将非分组字段用聚合函数包裹，确保SELECT中的字段要么是分组字段，要么是聚合结果：

public function currentRevision()
{
    return $this->hasOne(DocumentTypeRevision::class, 'document_type_id')
        ->ofMany(
            [
                'revision_number' => 'max',
                'revision_date' => 'max',
                'id' => 'max'
            ],
            function ($query) {
                $query->where('revision_date', '<=', now()->startOfDay());
            }
        );
}

原理说明 #

使用多字段ofMany时，Laravel默认生成的多层子查询中，revision_date和revision_number被直接选取但未分组，导致违反SQL模式规则。上述两种方式要么通过排序逻辑让Laravel生成正确的关联查询，要么确保所有SELECT字段都满足GROUP BY要求，从根源解决语法错误。

内容的提问来源于stack exchange，提问作者TUPKAP