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, May 09 '17 at 12:17

score 1 · Answer 1 · answered May 09 '17 at 13:33

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();

Laravel 5.4 Eloquent Query Optimization

1 Answers1