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?
