DEV Community

KILLALLSKYWALKER
KILLALLSKYWALKER

Posted on

Attack of the Messy Queries: Rise of the Query Builder

I used to build a job portal previously , the job search page only had a few filters — job title, location, and salary. The code was simple, everything inside controller, and it worked fine.

But later, product team kept adding more filters where slowly, the controller became very long and messy. Every new filter meant adding more if statement. The method easily reached 200+ lines. It was hard to maintain, and testing the filter logic was also painful.

That’s when I realised we need a better way to handle this which is we can use Laravel Query Builder to handle this thing .

Problem: Controller Too Messy

public function index(Request $request)
{
    $query = JobAdvertisement::query()->with('company');

    // Keyword / Title search
    if ($request->filled('keyword')) {
        $query->where(function ($q) use ($request) {
            $q->where('title', 'like', '%' . $request->keyword . '%')
              ->orWhere('description', 'like', '%' . $request->keyword . '%');
        });
    }

    # Location filter (city, country, remote)
    if ($request->filled('location')) {
        if ($request->location === 'remote') {
            $query->where('is_remote', true);
        } else {
            $query->where('location', 'like', '%' . $request->location . '%');
        }
    }

    # Salary range
    if ($request->filled('salary_min')) {
        $query->where('salary', '>=', $request->salary_min);
    }
    if ($request->filled('salary_max')) {
        $query->where('salary', '<=', $request->salary_max);
    }

    # Job type (full-time, part-time, contract, internship, freelance)
    if ($request->filled('job_type')) {
        $query->whereIn('job_type', (array) $request->job_type);
    }

    // Experience level (entry, mid, senior, etc.)
    if ($request->filled('experience_level')) {
        $query->whereIn('experience_level', (array) $request->experience_level);
    }

    // Industry / Category
    if ($request->filled('industry')) {
        $query->whereIn('industry', (array) $request->industry);
    }

    return $query->paginate(20);
}
Enter fullscreen mode Exit fullscreen mode

This works, but it is not nice because there's to many logic in the code , its harder to test and also if next time we want to add new filter it will add another line in this method .

The Solution Is Using Eloquent Builder

So we can create a builder , to make it short i will not provided all the method for builder .

class JobAdvertisementBuilder extends Builder
{
    public function withKeyword($keyword)
    {
        if (empty($keyword)) {
            return $this;
        }

        return $this->where(function ($q) use ($keyword) {
            $q->where('title', 'like', "%{$keyword}%")
              ->orWhere('description', 'like', "%{$keyword}%");
        });
    }

    public function withLocation($location)
    {
        if (empty($location)) {
            return $this;
        }

        if ($location === 'remote') {
            return $this->where('is_remote', true);
        }

        return $this->where('location', 'like', "%{$location}%");
    }

    public function withFilters(array $filters): self
    {
        return $this
            ->withKeyword($filters['keyword'] ?? null)
            ->withLocation($filters['location'] ?? null);
    }
}

Enter fullscreen mode Exit fullscreen mode

Once you create a builder we need to ensure that we use the builder that we create by overrides Laravel's default builder instantiation like this in the model

public function newEloquentBuilder($query) 
{
    return new JobAdvertisementBuilder($query);
}
Enter fullscreen mode Exit fullscreen mode

Since JobAdvertisementBuilder extends Builder, we inherit all the original Laravel query methods automatically no functionality is missing .

Its Time To Clean Up Controller

Now we already have the builder , it's time for use to clean up our controller . This is how we can use it in our controller .

# You can use withFilters
public function index(Request $request)
{
    $jobs = JobAdvertisement::withFilters($request->all())->paginate(15);
}

# Or you still can use dedicated method if you need 
public function index(Request $request)
{
    $jobs = JobAdvertisement::withLocation($request->location))->paginate(15);
}
Enter fullscreen mode Exit fullscreen mode

So in any case if you want to reuse any filter in any of you service also you can reuse the same method from the builder .

Closing

If you look closely, what we are doing here can be considered the Builder Pattern. You can read more about it in this books Refactoring Guru Design Patterns .

Top comments (0)