Search code examples
mysqllaraveleloquente-commerceeloquent-relationship

Single Query to get paginated data and all possible filters from the obtained results

I am writing a query to perform search for eCommerce. I need to meet 3 search criteria.

  1. Search by category id
  2. Search by keyword
  3. Search by product type (featured, hot, new)

Also i need to extract all possible filters to show user from the filtered results(not paginated results).

I have the following relationships.

products
products -> images,
products -> translation,
products -> categories,
products -> variants,
products -> brand
products -> category
products -> addons
products -> product_attributes

What i am doing is first get all products based on search criteria. Extract all possible filters from the obtained results. Then paginate the results using LengthAwarePaginator.

This approach works fine for small set of products. But now i am having more than 4k products. When my query is executed the memory is exhausted due to so many relations. I have reduced number of columns in product and all relations. Also processing time increases up to 6 secs which i can not afford.

These are the filters i need to extract from the obtained set 

attributes
brands
categories
min_price
max_price

The idea is that when user searches a category filters are displayed in side bar which fall under that category. When user selects some filters and apply search the new filtered results are obtained with pagination. Still i extract possible filters from new results.

I need suggestion as what to do in this situation. Please help.

Solution

  • I would suggest to have a separate paginated query for your products and a separate query to get possible filters based on user selection, get this information directly from DB and not to cook/filter data from laravel helper methods or in code. On DB side there are several methods to optimize query performance if relations are formed correctly and properly indexed. If you perform this extraction from code it will surely increases memory usage of server.

    Later on you can break your possible filters single query (which may have joins from relation table) in to multiple queries it may increase DB calls but will provide benefit in terms of performance.