英文:
how to Laravel eloquent having array
问题
你需要将Laravel中的having
条件从单一的等号改为whereIn
,以实现与你所需的SQL语句相匹配的条件。以下是相应的更改:
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'), function ($q) {
$q->where('user_id', auth()->id());
})
->orderBy('id', 'DESC')
->limit(1),
])
->having('last_state', 'IN', $data); // 将等号改为'IN'来匹配你所需的条件。
});
这样修改后,Laravel将生成具有HAVING last_state IN ('3', '5', '1')
条件的SQL查询,与你所需的SQL语句匹配。
英文:
how to type this sql in laravel eloquent 10
$data = array:2 [
0 => "3"
1 => "5"
2 => "1"
]
relations
public function followActionFollowStudent()
{
return $this->hasMany(FollowActionFollowStudent::class, 'follow_student_id', id');
}
i need sql as this below
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
i try this code
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);
});
it return 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` = '3')) having `last_user_action` = 6 limit 10 offset 0
The problem is Laravel returns
having `last_state` = '3'
i need return
having `last_state` in ('3','5','1')
答案1
得分: 2
以下是更新后的工作代码:
我替换了
->having('last_state', $data);
为
->havingRaw('last_state in ('.implode(',', $data).')');
所以完整的代码是:
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),
])
->havingRaw('last_state in ('.implode(',', $data).')');
});
英文:
the work code after update
i replace
->having('last_state', $data);
with
->havingRaw('last_state in ('.implode(',', $data).')');
so full code is
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),
])
->havingRaw('last_state in ('.implode(',', $data).')')
});
答案2
得分: 0
用以下替换 having('last_state', $data)
:havingRaw('last_state IN (?)', [ implode(',', $data)])
英文:
Replace having('last_state', $data)
with havingRaw('last_state IN (?)', [ implode(',', $data)])
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论