如何在Laravel中使用Eloquent查询数组

huangapple go评论57阅读模式
英文:

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)])

huangapple
  • 本文由 发表于 2023年6月2日 07:11:29
  • 转载请务必保留本文链接:https://go.coder-hub.com/76386254.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定