exists query does not return relevant result

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 the category with slug “tonery-cartridge”. Why this query returns an empty result? If I try to make inner join sql manually it works well as on the screenshot below:

Answer

I think that your queries are not equivalent. The SQL output from laravel does not join tables in it’s FROM clause, but in your manually constructed SQL statement you do a lot of inner joins in your FROM clause and on the resulting table you perform your operations, which is not true for the former.

Try the following

DB::table('products')
    ->join('category_product', 'category_product.product_id', '=', 'products.id')
    ->join('categories', 'category_product.category_id', '=', 'categories.id')
    ->whereRaw('products.name LIKE %can% AND categories.slug = "tonery-cartridge"')
    ->select('products.name', 'categories.slug')
    ->orderBy('created_at','DESC')
    ->take(10)
    ->get()

If you want to avoid using whereRaw, you can try the following.

DB::table('products')
    ->join('category_product', 'category_product.product_id', '=', 'products.id')
    ->join('categories', 'category_product.category_id', '=', 'categories.id')
    ->where([
              ['products.name, 'LIKE', '%' .  $value . '%'],
              ['categories.slug', '=', 'tonery-cartridge']])
    ->select('products.name', 'categories.slug')
    ->orderBy('created_at','DESC')
    ->take(10)
    ->get()

HTH