Laravel 5.4 Eloquent Query Optimization

Question

I want to optimize Laravel query for product listing. I need to display product listing as well as brands. Following is the code:

$searchTerm = 'Coffee';
$productListing = Product::where('title', 'like', '%'.$searchTerm.'%')->paginate(10);

I also need separate list for all brands for products returned by search.

Method 1:

Get all brand ids in array

$productBrandsArray = $productListing->pluck('brand_id')->toArray();

Problem is this will only get brands for 10 records because of products pagination

$productBrands = Brand::whereIn('brand_id', $productBrandsArray);

Method 2 (Sub-Query):

$productBrands = Brand::whereIn('brand_id', function ($query) use($searchTerm) {
$query->select('brand_id')
->from(with(new Product())->getTable())
->where(Product::getTableName().'.title', 'like', '%'.$searchTerm.'%');});

Currently I am using sub-query method to get results but I think its not optimized because same search query is executed multiple times.

Please suggest.

Thanks.

The problem lies with the wildcards, you can't optimize a query that uses LIKE '%text%' because it won't be able to use an index, so you need to remove the first wildcard in order to speed this query up. — Florian Humblot
– Florian Humblot, Commented May 9, 2017 at 12:17

Dinoop · Accepted Answer · 2017-05-09 13:33:26Z

1

Pagination is working in the bases of limit and offset, so you have to make a second query in order to get the whole brands. In the Method 1 to get product brands you can change the query as given below so you don't need get the brands id separately.

$productBrands = Brand::where('products.title', 'like', '%' . $searchTerm . '%')
                ->join("products", "brands.brand_id", "=", "products.brand_id")
                ->get();

answered May 9, 2017 at 13:33

Dinoop

4974 silver badges11 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel 5.4 Eloquent Query Optimization

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related