Code Review: Spatie Query Builder Implementation with Multiple Search Filters - Best Practices?

I'm working with the Spatie Laravel Query Builder package (v6) and would like some feedback on my current implementation. I'm creating a search functionality with two filters and want to know if there are any technical issues or improvements I should consider.

Here's my current code:

$request = \Illuminate\Http\Request::create("/users", "GET", [
  "filter" => [
    "search" => "j"
  ]
]);

QueryBuilder::for(User::Class, $request)
  ->allowedFilters([
    AllowedFilter::callback("search", function (Builder $query, $value) {
      $query->where(function (Builder $query) use ($value) {
        $query->whereAny(["name", "email"], "like", "%{$value}%");
      });
    }),
    AllowedFilter::callback("searchBy", function (Builder $query, $value) use (
      $request
    ) {
      $searchTerm = $request->input("filter.search");

      if ($searchTerm && in_array("all", $value)) {
        $query
          ->where("name", "like", "%{$searchTerm}%")
          ->orWhere("email", "like", "%{$searchTerm}%");
      }

      if (in_array("name", $value)) {
        $query->where("name", "like", "%{$searchTerm}%");
      }

      if (in_array("email", $value)) {
        $query->where("email", "like", "%{$searchTerm}%");
      }
    })
  ])
  ->get();

Tray2

7 months ago

Level 73

I would do something like this instead, just using when to check if a filter is passed.

    public function __invoke(Request $request)
    {
        return view('books.index')
            ->with([
                'books' => BookIndexView::query()
                    ->when($request['authors'], function ($query, $authors) {
                        $query->whereIn('author_id',
                            $this->numericStringToArray($authors));
                    })
                    ->when($request['published'], function ($query, $published) {
                        $query->where('published_year', $published);
                    })
                    ->when($request['genre'], function ($query, $genre) {
                        $query->where('genre', $genre);
                    })
                    ->when($request['format'], function ($query, $format) {
                        $query->where('format', $format);
                    })
                    ->when($request['search'], function ($query, $search) {
                        $query->where('title', 'LIKE',  "%$search%")
                        ->orWhere('author_name', 'LIKE', "%$search%")
                        ->orWhere('series', 'LIKE', "%$search%");
                    })
                    ->orderBy('author_name')
                    ->orderBy('series')
                    ->orderBy('part')
                    ->orderBy('published_year')
                    ->get(),
            ]);
    }

1 like

minaremonshaker

7 months ago

Level 6

Do you mean that instead of using an if condition in this query, I can use when for cleaner code?

Tray2

7 months ago

Level 73

yes

JussiMannisto

7 months ago

Level 50

Whenever you use wildcards around a search term, such as where foo like '%bar%', the entire table has to be scanned because no index can be utilized. This puts a lot of load on the database if the table is large.

It may be acceptable if this is some admin panel query that runs only occasionally. In any user-facing search, I'd use either full text indexes or wildcards only at the end: where foo like 'bar%'.

https://laravel.com/docs/12.x/migrations#available-index-types https://laravel.com/docs/12.x/queries#full-text-where-clauses

minaremonshaker

7 months ago

Level 6

Hi,
In my situation, I’m not dealing with searches through large blocks of text or lengthy paragraphs. Instead, I’m working with a users table that only has a few columns. That’s why I believe using foo% at the end should be fine. Do you agree?

Tray2

7 months ago

Best Answer

Level 73

foo% is fine, it will use indexes, however if you use %foo% then you might get some performance issues, and might be better off using full text indexes.

minaremonshaker

7 months ago

Level 6

Hi! I've implemented this improvement to the query and it's producing the desired results, but the query appears quite extensive. Is this typical? To streamline the controller, I utilized the package's custom function feature and created dedicated classes for each filter, resulting in this structure:

$users = QueryBuilder::for(User::class, $request)
    ->allowedFilters([
        AllowedFilter::callback('search', function (Builder $query, $value) use ($request) {
            $searchBy = $request->input('filter.searchBy');
            
            $query->when(!$searchBy || count($searchBy) == 0, function (Builder $query) use ($value) {
                $query->where(function (Builder $query) use ($value) {
                    $query->where('name', 'like', "%{$value}%")
                          ->orWhere('email', 'like', "%{$value}%");
                });
            });
        }),
        
        AllowedFilter::callback('searchBy', function (Builder $query, $value) use ($request) {
            $searchTerm = $request->input('filter.search');
            
            $query->when(in_array('all', $value), function (Builder $query) use ($searchTerm) {
                    $query->where('email', 'like', "%{$searchTerm}%")
                          ->orWhere('name', 'like', "%{$searchTerm}%");
                })
                ->when(in_array('name', $value), function (Builder $query) use ($searchTerm) {
                    $query->where('name', 'like', "%{$searchTerm}%");
                })
                ->when(in_array('email', $value), function (Builder $query) use ($searchTerm) {
                    $query->orWhere('email', 'like', "%{$searchTerm}%");
                });
        }),
    ])
    ->get();

$users = QueryBuilder::for(User::class, $request)
    ->allowedFilters([
        AllowedFilter::custom('search',new FilterSearchUsers),
        AllowedFilter::custom('searchBy', new FilterSearchByUsers )
    ])
    ->get();

Please or to participate in this conversation.