SQL exists query does not return relevant result

  eloquent, laravel, sql

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 on the screenshot below:

enter image description here

Source: Laravel

Leave a Reply