Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.
I have a Laravel application with this Eloquent query:
$products = Product::where('name', 'LIKE', "%{$value}%")
->whereHas('categories', function($q) {
$q->where( 'slug', 'tonery-cartridge' );
})->with('manufacturer')
->with('sm_image')
->orderBy('created_at','DESC')
->take(10)
->get();
This code generates the sql command like:
select * from `products` where `name` LIKE '%can%'
and exists (
select * from `categories` inner join `category_product`
on `categories`.`id` = `category_product`.`category_id`
where `products`.`id` = `category_product`.`product_id`
and `slug` = 'tonery-cartridge'
)
order by `created_at` desc limit 10
I am sure there are products which name contains "can" string and which belongs to category with slug "tonery-cartridge". Why this query returns empty result? If I try to make inner join sql manually it works well as you can see on the screenshot here: https://stackoverflow.com/questions/65080957/sql-exists-query-does-not-return-relevant-result?noredirect=1#comment115057625_65080957
Change it to this
$products = Product::where('LOWER(name)', 'LIKE', '%' . strtolower($value) . '%')
->whereHas('categories', function($q) {
$q->where( 'slug', 'tonery-cartridge' );
})->with('manufacturer')
->with('sm_image')
->orderBy('created_at','DESC')
->take(10)
->get();
You tried to find can but in your database is Canon and CANON
This should not be the problem cause table is in utf8_slovak_ci collation it means it is case insensitive. Also this command throws an error unknow column LOWER(name)
Have a test site on a live web is really big amateurism in your company.
But this is not the issue.
On the big project never ever use whereHas. It's way to hell. Instead use joins.
But then I lost the Eloquent relations as objects .... I would like to avoid it.
whereHas and eager loading is complete different thing.
Enable models tab in debugbar, I just want to know how many models you retrieved. Where is caching?
How to enable models tab in debugbar? First time I hear about models tab.
In config/debugbar.php
'collectors' => [
//
'models' => true, // Display models
],
Ok so how many model retrieved from what?
If you know the category then I would start with that
$category = Category::where( 'slug', 'tonery-cartridge' )->first();
$products = $category->products()
->where('name', 'LIKE', "%{$value}%")
->with('manufacturer')
->with('sm_image')
->latest()
->take(10)
->get();
Thank you $category->products() works.
What the hell is it? It is about 50 000 model on homepage.
and 126 queries and 235MB of memory for one page
Yes I know...
Yes it's to much. It's unusable in production.
One question. What should be the right way to make this query? Can I make a DB inner join get products ids and then call
Product::whereIn('id', $ids);
with necessary relations? Is it ok or not?
Please or to participate in this conversation.