Laravel Eloquent whereHas function

You can display the last SQL query with a command (if you wanted to see it)

It JOINS the table and adds a WHERE condition?

If you need even more power, you may use the whereHas and orWhereHas methods to put "where" conditions on your has queries:

$posts = Post::whereHas('comments', function($q)
{
    $q->where('content', 'like', 'foo%');

})->get();

Ref: http://laravel.com/docs/4.2/eloquent#querying-relations

I can't understand how this query works. Is count statement join tables? Is this more efficient than normal join?

SELECT
 *
FROM
 `products`
WHERE
 (
  SELECT
   count(*)
  FROM
   `categories`
  WHERE
   `products`.`category_id` = `categories`.`id`
  AND `slug` = ?
 ) >= 1
AND `slug` <> ?

Try it, run it in MySQL CLI and see the execution time difference :)

The query you posted is a nested select also known as a subquery. So really there are two queries at work here, an outer query and an inner query. Basically, the (inner) SELECT statement evaluates to a boolean value -- this is the reason for the count and the >= 1 condition. Since the inner query results in a true/false value, the outer where clause sees it as just another condition. Hope that helps!

This page explains subqueries in further detail.