Search Function with Multiple Conditions in Laravel

Question

I tried to make a search function based on 4 requests; categories, location, keyword, and sorting. The search function with 3 requests (location, categories, and sorting) works well and has correct output.

But the problem is, when my website sent request with keywords data, the output results are only the result from keywords request only, when I try to sent a location / categories requests, the output does not change (only the output of the keyword request only)

Here is the code I write:

    public function getBy(Request $request) {
        $categories = $request->all()['categories'];
        $location   = $request->all()['location'];
        $sorting    = $request->all()['sorting'];
        $keyword    = $request->all()['keyword'];

        //--- sorting code ---

        //keyword request
        if ($keyword === null) {
          $jobs = Job::query();
        } else {
          $jobs = Job::query()->where('job_title', 'LIKE', "%{$keyword}%")
            ->orWhere('title', 'LIKE', "%{$keyword}%")
            ->orWhere('description', 'LIKE', "%{$keyword}%")
            ->orWhereHas('users', function ($q) use ($keyword) {
              $q->where('company_name', 'LIKE', "%{$keyword}%");
            })->orWhereHas('categories', function ($q) use ($keyword) {
              $q->where('category', 'LIKE', "%{$keyword}%");
            })->orWhereJsonContains('tags', $keyword);
        }

        //location and categories request
        if ($location === [] && $categories === []) {
          $jobs;
        } elseif ($location === []) {
          $jobs->whereHas('categories', function ($q) use ($categories) {
            $q->whereIn('slugs', $categories);
          });
        } elseif ($categories === []) {
          $jobs->whereHas('location', function ($q) use ($location) {
            $q->whereIn('city_name', $location);
          });
        } else {
          $jobs->whereHas('categories', function ($q) use ($categories) {
            $q->whereIn('slugs', $categories);
          })->whereHas('location', function ($q) use ($location) {
            $q->whereIn('city_name', $location);
          });
        }

        //return as JSON
      }

Answer 1

The main issue with your code is your locations and categories search query were not setting $jobs again. It should look like this:

 $jobs = $jobs->whereHas('categories', function ($q) use ($categories) {
     $q->whereIn('slugs', $categories);
 });

This will set the $jobs variable to include the new conditions of the query.

I would also suggest not checking if $locations or $categories are empty, as they if statements for each are dealing with the condition already. Here is some refactored code:

public function getBy(Request $request) {
    $sorting    = $request->all()['sorting'];

    //--- sorting code ---

    //keyword request
    if (! $keywords = request('keywords')) {
        $jobs = Job::query();
    } else {
        $jobs = Job::query()->where('job_title', 'LIKE', "%{$keyword}%")
            ->orWhere('title', 'LIKE', "%{$keyword}%")
            ->orWhere('description', 'LIKE', "%{$keyword}%")
            ->orWhereHas('users', function ($q) use ($keyword) {
                $q->where('company_name', 'LIKE', "%{$keyword}%");
            })->orWhereHas('categories', function ($q) use ($keyword) {
                $q->where('category', 'LIKE', "%{$keyword}%");
            })->orWhereJsonContains('tags', $keyword);
    }

    foreach (['categories', 'locations'] as $field) {
        if ($request->filled($field)) {
            $jobs = $jobs->whereHas($field, function ($q) use ($field) {
                $q->whereIn('slugs', request($field));
            });
        }
    }

    return $jobs->get()->toArray();
}

Here we loop through the categories and locations request data and see if the data is ->filled() (is defined and not empty). If so, we run the query.

Answer 2

Try this

 public function getBy(Request $request) {
    $categories = $request->categories;
    $location   = $request->location;
    $sorting    = $request->sorting;
    $keyword    = $request->keyword;

    //--- sorting code ---


    $jobs = Job::query();

    //keyword request
    if ($keyword !== null) {
      $jobs = $jobs->where('job_title', 'LIKE', "%{$keyword}%")
        ->orWhere('title', 'LIKE', "%{$keyword}%")
        ->orWhere('description', 'LIKE', "%{$keyword}%")
        ->orWhereHas('users', function ($q) use ($keyword) {
          $q->where('company_name', 'LIKE', "%{$keyword}%");
        })->orWhereHas('categories', function ($q) use ($keyword) {
          $q->where('category', 'LIKE', "%{$keyword}%");
        })->orWhereJsonContains('tags', $keyword);
    }

    //location and categories request
    if ($location === []) {
      $jobs = $jobs->whereHas('categories', function ($q) use ($categories) {
        $q->whereIn('slugs', $categories);
      });
    } elseif ($categories === []) {
      $jobs = $jobs->whereHas('location', function ($q) use ($location) {
        $q->whereIn('city_name', $location);
      });
    } else {
      $jobs = $jobs->whereHas('categories', function ($q) use ($categories) {
        $q->whereIn('slugs', $categories);
      })->whereHas('location', function ($q) use ($location) {
        $q->whereIn('city_name', $location);
      });
    }

    return $jobs->get();
  }