How can I use whereHas on a relation to the same table with Eloquent Query Builder?

Question

I'm trying to run this query:

$products = $this->product_model->where(function($query) use ($key) {
    $query->whereHas('categories', function ($category) use ($key) {
        $category->where('key', $key);
    });
    $query->orWhereHas('parent.categories', function ($category) use ($key) {
        return $category->where('key', $key);
    });
});

The parent relation is another product, so it's from the same table. The problem I'm having is in the query that this produces:

SELECT *
FROM `products`
WHERE (
    (SELECT count(*)
     FROM `categories`
     INNER JOIN `category_product` ON `categories`.`id` = `category_product`.`category_id`
     WHERE `category_product`.`product_id` = `products`.`id`
     AND `key` = 'mens'
    ) >= 1
 OR
    (SELECT count(*)
     FROM `products` AS `self_30ec5d4782a83841add518f618b9f59e`
     WHERE `self_30ec5d4782a83841add518f618b9f59e`.`id` = `products`.`parent_product_id`
     AND
         (SELECT count(*)
          FROM `categories`
          INNER JOIN `category_product` ON `categories`.`id` = `category_product`.`category_id`
          WHERE `category_product`.`product_id` = `products`.`id`
          AND `key` = 'mens'
         ) >= 1
    ) >= 1
)

In the sub-query after the OR I need this line:

WHERE `category_product`.`product_id` = `products`.`id`

To be this:

WHERE `category_product`.`product_id` = `self_30ec5d4782a83841add518f618b9f59e`.`id`

When I run this SQL on the database I get the correct result:

SELECT *
FROM `products`
WHERE (
    (SELECT count(*)
     FROM `categories`
     INNER JOIN `category_product` ON `categories`.`id` = `category_product`.`category_id`
     WHERE `category_product`.`product_id` = `products`.`id`
     AND `key` = 'mens'
    ) >= 1
 OR
    (SELECT count(*)
     FROM `products` AS `self_30ec5d4782a83841add518f618b9f59e`
     WHERE `self_30ec5d4782a83841add518f618b9f59e`.`id` = `products`.`parent_product_id`
     AND
         (SELECT count(*)
          FROM `categories`
          INNER JOIN `category_product` ON `categories`.`id` = `category_product`.`category_id`
          WHERE `category_product`.`product_id` = `self_30ec5d4782a83841add518f618b9f59e`.`id`
          AND `key` = 'mens'
         ) >= 1
    ) >= 1
)

But I'm not sure how to make it do that in my PHP code. Also, is this the intended SQL output? Shouldn't it be doing what I want it to do? Since the sub-query is within the whereHas?

Answer 1

This is a bug in Laravel. has()/whereHas() does not handle conditions on self relationships or nested self relationships correctly.

I have submitted a pull request to correct this issue. You can view it here.

I don't know if it will be accepted, but you can take a look at it to see the code that was changed, and make the decision as to whether you want to manually update Laravel yourself or not. You can also follow it and wait to see the outcome.