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')
2
Answers
the work code after update
i replace
with
so full code is
Replace
having('last_state', $data)
withhavingRaw('last_state IN (?)', [ implode(',', $data)])