skip to Main Content

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


  1. Chosen as BEST ANSWER

    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. Replace having('last_state', $data) with havingRaw('last_state IN (?)', [ implode(',', $data)])

    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search