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

}