CODEWITHSUNDEEP
phplaravelapifilter
Vagif Aghayev
Vagif Aghayev

Reputation: 411

Laravel complicated filter query

I need to write API query which consists of "status", "category_id", "city_id" fields, and there are a lot of cases:

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)

I have already tried to write this query, however, confused in the number of statements (the code is not working correctly, there is also district_id, but for simplicity of example I did not mention it):

        $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);
            }
        } elseif ($cat == "" || $cat == []) {
            $issues = $issues->where('status', $status)->where('city_id', $city)->where('district_id', $dist);
        } elseif ($status == "" || $status == []) {
            $issues = $issues->where('category_id', (int)$cat)->where('city_id', $city)->where('district_id', $dist);
        }

        $issues = $issues->get();

Is there any way not to use so many if-else cases and make code looks cleaner and work properly? Thanks everyone for answers in advance!

Upvotes: 1

Views: 158

Answers (2)

Vagif Aghayev
Vagif Aghayev

Reputation: 411

I wrote better version of query, however the disadvantage is I have to pass status or category_id as an array like this + if city_id AND/OR district_id is null, then no data returns:

{
    "city_id" : 5,
    "district_id" : 9,
    "category_id" : [5,4],
    "status" : ["REJECTED"]
}

And here is code:

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

Upvotes: 1

cetver
cetver

Reputation: 11829

Force convert to array

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

Upvotes: 0

Related Questions