Laravel 复杂的过滤查询

Question

我需要编写由“status”、“category_id”、“city_id”组成的 API 查询领域，而且有很多案例：

1) status is an array => ['OPEN','CLOSED'] OR category_id is an array => [1,2,4] AND city_id = 7 (could be any integer)

2) status is an array => ['OPEN','CLOSED'] OR category_id is an integer => 2 AND city_id = 7 (could be any integer)

3) status is a string => 'OPEN' OR category_id is an array => [1,2,4] AND city_id = 7 (could be any integer)

4) status is an array => ['OPEN','CLOSED'] AND city_id = 7 (could be any integer) 

5) category_id is an array => [1,2,4] AND city_id = 7 (could be any integer) 

6) status is a string => 'OPEN' AND city_id = 7 (could be any integer) 

7) category_id is an integer => 1 AND city_id = 7 (could be any integer) 

我已经尝试编写此查询，但是，对语句数量感到困惑（代码无法正常工作，还有district_id，但为了简单起见，我没有提到）：

        $cat = cleanString(request()->get('category_id'));

        $status = cleanString(request()->get('status'));

        $city = cleanString(request()->get('city_id'));

        $dist = cleanString(request()->get('district_id'));

        if ($cat != null && $status != null) {

            if (is_array($cat) && is_array($status)) {

                $issues = $issues->whereIn('category_id', $cat)->orWhereIn('status', $status)->where('city_id', $city)->where('district_id', $dist);

            } elseif (is_array($cat)) {

                $issues = $issues->whereIn('category_id', $cat)->where('status', $status)->where('city_id', $city)->where('district_id', $dist);

            } elseif (is_array($status)) {

                $issues = $issues->whereIn('status', $status)->where('category_id', $cat)->where('city_id', $city)->where('district_id', $dist);

            } elseif (is_string($cat) && is_string($status)) {

                $issues = $issues->where('category_id', $cat)->where('status', $status)->where('city_id', $city)->where('district_id', $dist);

            }

有没有办法不使用这么多 if-else 情况并使代码看起来更干净并正常工作？ 提前感谢大家的解答！

Answer 1

我编写了更好版本的查询，但缺点是我必须将 status 或 Category_id 作为数组传递，如下所示 + 如果 city_id AND/OR District_id 为 null，则没有数据返回：{    "city_id" : 5,    "district_id" : 9,    "category_id" : [5,4],    "status" : ["REJECTED"]}这是代码： if ($cat != null && $status != null) {                $issues = $issues->where('city_id', $city)->where('district_id', $dist)                    ->where(function ($q) {                        $q->whereIn('category_id', cleanString(request()->get('category_id')))                            ->orWhereIn('status', cleanString(request()->get('status')));                    });            } elseif ($cat == "" || $cat == []) {                $issues = $issues->where('city_id', $city)->where('district_id', $dist)                    ->where(function ($q) {                        $q->whereIn('status', cleanString(request()->get('status')));                    });            } elseif ($status == "" || $status == []) {                $issues = $issues->where('city_id', $city)->where('district_id', $dist)                    ->where(function ($q) {                        $q->whereIn('category_id', cleanString(request()->get('category_id')));                    });            }

Answer 2

强制转换为数组$filters = [   'category_id' => (array) cleanString(request()->get('category_id', [])),   'status' => (array) cleanString(request()->get('status', [])),   ];$filters = array_filter($filters);foreach($filters as $key => $val) {   $issues->orWhereIn($key, $val);}