SQL转Laravel Eloquent报错求助：关联子查询转换问题

原SQL语句 #

select 
    `d1`.*, `a1`.`user_uuid` as `activity_user` 
from 
    `directors` as `d1` 
left join 
    `activities` as `a1` 
on `a1`.`entity_uuid` = `d1`.`uuid` 
and `a1`.`created_at` = (
    select 
        max(`created_at`) 
    from
        `activities` as `a2`
    where `a2`.`entity_uuid`=`d1`.`uuid`
)
where `a1`.`user_uuid`='4f858f46-2d7e-41c8-a185-c9c4a4b506d1'
group by `a1`.`entity_uuid` 
order by `d1`.`updated_at` 
desc limit 100;

错误的Eloquent代码 #

DB::table('directors')
->select('directors.*')
->leftJoin('activities',function($join) {
    $join->on('a1.entity_uuid','=','directors.uuid')
    ->on('a1.created_at','=',function($query) {
        $query->from('activities')
            ->select(DB::raw('max'('activities.created_at'))
            ->where('activities.entity_uuid','=',DB::raw('d1.uuid'));
    });
}
->where('activities.user_uuid','=','4f858f46-2d7e-41c8-a185-c9c4a4b506d1')
->groupBy('activities.entity_uuid')
->orderBy('directors.updated_at','desc')
->get();

报错信息 #

TypeError: stripos(): Argument #1 ($haystack) must be of type string, Closure given in file C:\xampp\htdocs\crmMembers\backend\vendor\laravel\framework\src\Illuminate\Database\Grammar.php on line 62

问题分析与修正代码 #

核心问题点 #

• 未给表设置对应别名，原SQL中directors用d1、activities用a1，但错误代码里未定义，导致引用别名无效
• DB::raw语法错误：max'('activities.created_at')括号位置写错，正确格式应为max(activities.created_at)
• leftJoin的闭包未闭合，缺少结束括号
• select语句未包含原SQL要求的a1.user_uuid as activity_user字段
• join的on条件中直接传入闭包作为值，Laravel无法正确解析，需调整子查询写法

修正后的代码 #

DB::table('directors as d1')
    ->select('d1.*', 'a1.user_uuid as activity_user')
    ->leftJoin('activities as a1', function ($join) {
        $join->on('a1.entity_uuid', '=', 'd1.uuid')
             ->on('a1.created_at', '=', function ($query) {
                 $query->from('activities as a2')
                       ->selectRaw('max(a2.created_at)')
                       ->whereColumn('a2.entity_uuid', 'd1.uuid');
             });
    })
    ->where('a1.user_uuid', '=', '4f858f46-2d7e-41c8-a185-c9c4a4b506d1')
    ->groupBy('a1.entity_uuid')
    ->orderBy('d1.updated_at', 'desc')
    ->limit(100)
    ->get();

关键修正说明 #

1. 给表添加与原SQL一致的别名d1和a1，确保字段引用正确
2. 使用selectRaw替代错误的DB::raw写法，子查询中用whereColumn关联主表uuid，避免语法冲突
3. 补充leftJoin闭包的结束括号，修复代码结构错误
4. 在select中添加a1.user_uuid as activity_user，还原原SQL的查询字段
5. 添加limit(100)，补全原SQL的限制条件

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