Laravel Question posted in
The official Laravel Documentation can be found here.

How to filter data in Laravel.11

I’m working on a filtering system for products using laravel Laravel Framework 11.20.0 & PHP 8.3.10,
and wanted to get your thoughts on the best approach.

Here’s the scope query I’ve added to the Product model

public function applyFilters($query, Request $request)
{
    if ($request->has('price_from')) {
        $query->where('product_price', '>=', $request->get('price_from'));
    }

    if ($request->has('price_to')) {
        $query->where('product_price', '<=', $request->get('price_to'));
    }

    if ($request->has('store')) {
        $query->where('store_id', '=', $request->get('store'));
    }

    if ($request->has('category')) {
        $query->whereHas('category', function ($q) use ($request) {
            $q->where('category_name', $request->get('category'));
        });
    }

    return $query;
}

It works, but I was wondering if you have any tips on how I could make this filtering more efficient and faster, especially when dealing with large datasets. Are there any best practices or optimization techniques you’d recommend for improving the performance of this query?

Tags:

2

Answers


  1. 0

    If you have relationships in your query, such as category, I think that you may use eager loading to avoid the N+1 query problem. For instance: $query->with("category");

    Or you may use the existing (built-in) pagination method for larger datasets. For example, you can set up 15 items for each page.

    Login or Signup to reply.
  2. 0

    To optimize the queries, here are a few things you can try:

    1. Consider Indexing Your Columns: Columns like product_price, store_id, and category_name can be indexed in your database. It can speed things up a lot. That being said, you don’t need to index everything – just the columns that you frequently use in WHERE clauses.
    2. Switch to JOIN for Categories: Instead of whereHas() use a join() to filter by categories. It’s usually faster.
    3. Eager Load Relationships: If you need related data, load it with with('category') to avoid extra queries later.
    4. Select Only What You Need: Use select() to get only the columns you need, which cuts down on data transfer.
    5. Combine Conditions: If you’re filtering by price range, combine the conditions to simplify the query.

    The final result might end up looking something like this:

    public function applyFilters($query, Request $request)
{
    if ($request->has('price_from') || $request->has('price_to')) {
        $query->whereBetween('product_price', [
            $request->get('price_from', 0),
            $request->get('price_to', PHP_INT_MAX)
        ]);
    }

    if ($request->has('store')) {
        $query->where('store_id', '=', $request->get('store'));
    }

    if ($request->has('category')) {
        $query->join('categories', 'categories.id', '=', 'products.category_id')
              ->where('categories.category_name', $request->get('category'));
    }

    return $query;
}

    Just so you know, I didn’t include a select() or with('category') here because they don’t fit in the filter method.

    By the way, there’s this awesome package from Spatie called Laravel Query Builder that I really like. It lets you filter, sort, and include Eloquent relations based on a request and even follows the JSON API spec. Worth checking out if you’re interested! 🙂

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search