Laravel Eloquent 10中如何实现含IN条件的HAVING子句查询

问题描述 #

目标SQL #

select `follow_students`.*, 
(select `user_id` 
from `follow_action_follow_student` 
where `follow_action_follow_student`.`follow_student_id` = `follow_students`.`id` order by `id` desc limit 1) as `last_user_action` 
from `follow_students`
 where exists (select * from 
`follow_action_follow_student` 
where `follow_students`.`id` = `follow_action_follow_student`.`follow_student_id`) and (exists (select *, (select `follow_action_id` 
from `follow_action_follow_student` 
where `follow_action_follow_student`.`follow_student_id` = `follow_students`.`id` and `user_id` = 6
 order by `id` desc limit 1) as `last_state` 
from `follow_action_follow_student` where `follow_students`.`id` = `follow_action_follow_student`.`follow_student_id` and `follow_action_id` in ('3', '5', '1') having `last_state` in ('3', '5', '1') )) having `last_user_action` = 6

现有数据 #

$data = [
  "3",
  "5",
  "1"
];

模型关联 #

public function followActionFollowStudent()
{
    return $this->hasMany(FollowActionFollowStudent::class, 'follow_student_id', 'id');
}

尝试的代码及问题 #

尝试的Eloquent代码：

FollowStudent::withTrashed()->whereHas('followActionFollowStudent', function ($q) use ($data) {
    $q->whereIn('follow_action_id', $data)
        ->addSelect(['last_state' => FollowActionFollowStudent::select('follow_action_id')
            ->whereColumn('follow_action_follow_student.follow_student_id', 'follow_students.id')
            ->when(! auth()->user()->hasRole('isAdmin'), fn ($q) => $q->where('user_id', auth()->id()))
            ->orderBy('id', 'DESC')
            ->limit(1),
        ])
        ->having('last_state', $data);
});

生成的SQL中HAVING子句错误，变成：

having `last_state` = '3'

实际需要的是：

having `last_state` in ('3','5','1')

解决方案 #

核心修复：用havingIn替代having #

Laravel Eloquent提供havingIn方法专门处理HAVING子句的IN条件，同时需要补全目标SQL中last_user_action的查询逻辑，完整代码如下：

$userId = auth()->id();
$isAdmin = auth()->user()->hasRole('isAdmin');

$query = FollowStudent::withTrashed()
    // 添加目标SQL中的last_user_action字段
    ->addSelect([
        'last_user_action' => FollowActionFollowStudent::select('user_id')
            ->whereColumn('follow_action_follow_student.follow_student_id', 'follow_students.id')
            ->orderBy('id', 'DESC')
            ->limit(1)
    ])
    // 实现第一个EXISTS条件：存在关联记录
    ->whereHas('followActionFollowStudent')
    // 实现第二个EXISTS条件：满足follow_action_id范围且last_state在指定列表
    ->whereHas('followActionFollowStudent', function ($q) use ($data, $userId, $isAdmin) {
        $q->whereIn('follow_action_id', $data)
            ->addSelect([
                'last_state' => FollowActionFollowStudent::select('follow_action_id')
                    ->whereColumn('follow_action_follow_student.follow_student_id', 'follow_students.id')
                    ->when(!$isAdmin, fn ($q) => $q->where('user_id', $userId))
                    ->orderBy('id', 'DESC')
                    ->limit(1)
            ])
            // 使用havingIn生成IN条件的HAVING子句
            ->havingIn('last_state', $data);
    })
    // 实现外层的HAVING条件
    ->having('last_user_action', '=', $userId);

// 获取最终结果
$results = $query->get();

代码说明 #

1. havingIn方法：直接传递数组参数，自动生成last_state IN ('3','5','1')的SQL语法，解决原代码中having将数组转为单个值的问题。
2. 补全字段查询：通过addSelect添加last_user_action的子查询，完全匹配目标SQL的字段要求。
3. 拆分条件逻辑：用两次whereHas分别实现目标SQL中的两个EXISTS条件，代码结构更清晰。
4. 外层HAVING：添加->having('last_user_action', '=', $userId)实现目标SQL最后的having last_user_action = 6条件。

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