Home > Net >  Laravel - Loop data filtering in where clauses
Laravel - Loop data filtering in where clauses

Time:05-30

So I made a filter for my site. Using this filter user can search for tasks by name, categories (user can choose multiple categories by selecting checkboxes), city id and other stuff. Here's the code:

<?php

namespace App\Http\Services;

use App\Models\Task;

use Carbon\Carbon;

class SearchTaskService {
  public function execute(array $searchParameters) {
    $categories = $searchParameters['category'] ?? null;
    $is_remote = $searchParameters['remote_job'] ?? null;
    $is_no_responses = $searchParameters['no_responses'] ?? null;
    $period = $searchParameters['time'] ?? null;
    $name = $searchParameters['name'] ?? null;
    $city_id = $searchParameters['city_id'] ?? null;
    $category_id = $searchParameters['category_id'] ?? null;

    $tasks = Task::all();

    $tasks = Task::when($categories, function($query, $categories) {
        return $query->whereIn('category_id', $categories);
    })->when($is_remote, function($query) {
        return $query->where('remote', 1);
    })->when($is_no_responses, function($query) {
        return $query->withCount('feedbacks')->having('feedbacks_count', '=', 0);
    })->when($period === "in_a_day", function($query) {
        return $query->where('deadline', '<=', Carbon::now()->addDay());
    })->when($period === "in_a_week", function($query) {
        return $query->where('deadline', '<=', Carbon::now()->addWeek());
    })->when($period === "in_a_month", function($query) {
        return $query->where('deadline', '<=', Carbon::now()->addMonth());
    })->when($name, function($query, $name) {
        return $query->where('title', 'LIKE', '%'.$name.'%');
    })->when($city_id, function($query, $city_id) {
        return $query->where('city_id', $city_id);
    })->when($category_id, function($query, $category_id) {
        return $query->where('category_id', $category_id);
    });

    return $tasks
  }
}

This filter works fine, but I know for sure the code can be refactored, because it looks like trash (too big). Is there a way to filter the data in foreach or any other loop? It would be easy to loop this if every query was like "where", but sometimes it's "whereIn" or "withCount", so the queries are definitely not the same.

CodePudding user response:

Another way of doing it would be using pipeline.

You can define individual filters in separate classes (makes testing easier), like:

<?php

namespace App\Filters;

class ByCategories
{
    public function handle($query, $next)
    {
        if(request()->has('categories') {
            $query->whereIn('category_id', request()->categories);
        }
        
        return $next($query);
    }
}
<?php

namespace App\Filters;

class IsRemoteJob
{
    public function handle($query, $next)
    {
        if(request()->has('remote_job') {
            $query->where('remote', 1);
        }

        return $next($query);
    }
}

And so on for all your filters like HasNoResponse, ByPeriod, ByName, ByCityId, ByCategoryId - each filter class should have handle method.

Then in your SearchTaskService you can have execute defined as


<?php

namespace App\Http\Services;

use Illuminate\Pipeline\Pipeline;

class SearchTaskService
{
    public function execute()
    {
        $query = Task::query();

        return app(Pipeline::class)
            ->send($query)
            ->through([
                \App\Filters\ByCategories::class,
                \App\Filters\IsRemoteJob::class,
                \App\Filters\HasNoResponse::class,
                \App\Filters\ByPeriod::class,
                \App\Filters\ByName::class,
                \App\Filters\ByCityId::class,
                \App\Filters\ByCategoryId::class,
            ])
            ->thenReturn()
            ->get();
    }
}

Note: Here you will have to access the request() and get the search parameter directly from request in each of the Filter classes instead of using $searchParameters array.

CodePudding user response:

You might get something from my code.

public function filterRecords($query, $request, $columns = [], $idType = 'code')
{
$data   = $request->all();
$ids    = $request->input('ids');

foreach ($data as $key => $value) {
    if ($value !== null && $value !== '') {
        if (gettype($value) === 'string') {
            if (str_contains($value, 'like@@')) {
                $likeValue = substr($value, 6);
                $query = $query->where($key, 'like', '%' . $likeValue . '%');
            } else if (str_contains($value, 'exact@@')) {
                $exactValue = substr($value, 7);
                $query = $query->where($key, $exactValue);
            }
        }


        if (gettype($value) === 'array' && \in_array('symbol', $value) && $value[2] !== 'undefined') {  // For smaller than and greater than operations
            if ($value[2] !== null && $value[2] !== 'undefined' && in_array($value[1], ['<=', '>=', '==', '>', '<']))
                $query = $query->where($key, $value[1], $value[2]);
            continue;
        }

        if (gettype($value) === 'array' && in_array('range', $value)) {    // For all betweens (date, range)
            $isDate = in_array('date', $value) ? true : false;
            $isDate ? array_splice($value, 0, 2) : array_splice($value, 0, 1);

            if (count($value) === 1)
                $value[] = $isDate ? '1800-11-11' : PHP_INT_MIN;

            $value[0]   = is_null($value[0]) && !$isDate ? PHP_INT_MIN : $value[0];
            $value[1]   = is_null($value[1]) && !$isDate ? PHP_INT_MAX : $value[1];

            $value[0]   = is_null($value[0]) && $isDate ? '1800-11-11' : $value[0];
            $value[1]   = is_null($value[1]) && $isDate ? '4000-12-12' : $value[1];

            $from       = $isDate ? date(min($value)) : min($value);
            $to         = $isDate ? date(max($value)) : max($value);
            $query      = $isDate ? $query->whereBetween(DB::raw("DATE($key)"), array($from, $to)) : $query->whereBetween($key, array($from, $to));
            continue;
        }

        if ($key === 'include' && $ids !== null) {
            $query = $value == 1 ? $query->whereIn($idType, $ids) : $query->whereNotIn($idType, $ids);    // For ID and code
        }
    }
}
return $query;

}

$columns is the table columns which you want to search in.

Take a look and you might get idea to build your own. I hope this helps you.

  • Related