I am trying to use the orWhereHas method in Laravel with nested relationships, but I am not getting the expected result. I do not understand how to use this method?
My relationship is set up as follows:
Product -> hasOne uniqueItem -> hasMany fulfillmentCenterUniqueItems -> hasMany skus -> hasOne uniqueItem -> hasMany fulfillmentCenterUniqueItems
I am trying to test the whereHas and orWhereHas by retrieving products from a database containing uniqueItems that have uniqueItemFulfillmentCenter with id = 7089 OR products that contain sku that contains a uniqueItem that has uniqueItemFulfillmentCenter with id = 7412 .
Based on the data in my database, the result of this query should be two products. Product identifiers 105 and 239.
Here's the Eloquent code I'm using:
$product = Spire_models\Product ::whereHas('uniqueItem.fulfillmentCenterUniqueItems', function ($query) { $query->where('id', 7089); }) ->orWhereHas('skus.uniqueItem.fulfillmentCenterUniqueItems', function ($query) { $query->where('id', 7412); }) ->get()->toArray();
For some reason, this returns product id 105 instead of 105 and 239. The generated sql from this function:
select * from `products` where `products`.`deleted_at` is null and (select count(*) from `unique_items` where `products`.`unique_item_id` = `unique_items`.`id` and (select count(*) from `fulfillment_center_unique_items` where `fulfillment_center_unique_items`.`unique_item_id` = `unique_items`.`id` and `id` = 7089 and `fulfillment_center_unique_items`.`deleted_at` is null) >= 1 and `unique_items`.`deleted_at` is null) >= 1 and (select count(*) from `skus` where `skus`.`product_id` = `products`.`id` and (select count(*) from `unique_items` where `skus`.`unique_item_id` = `unique_items`.`id` or (select count(*) from `fulfillment_center_unique_items` where `fulfillment_center_unique_items`.`unique_item_id` = `unique_items`.`id` and `id` = 7412 and `fulfillment_center_unique_items`.`deleted_at` is null) >= 1 and `unique_items`.`deleted_at` is null) >= 1 and `skus`.`deleted_at` is null) >= 1
Is this sql generated incorrectly, or am I using the orWhereHas method orWhereHas ? For me, it does not look like the OR statement fits correctly in sql.
If I remove the orWhereHas method, everything will work as expected. For example, if I ran this:
$product = Spire_models\Product ::whereHas('uniqueItem.fulfillmentCenterUniqueItems', function ($query) { $query->where('id', 7089); }) ->get()->toArray();
I will correctly return product id 105. If I run this:
$product = Spire_models\Product ::whereHas('skus.uniqueItem.fulfillmentCenterUniqueItems', function ($query) { $query->where('id', 7412); }) ->get()->toArray();
I am returning product identifier 239 correctly. Thus, the individual parts of the request work correctly, but it seems that when I try to combine them with orWhereHas , I get unexpected results. Any idea why?
EDIT
According to the comments, it seems like this is a bug. I was able to get around this temporarily by rewriting the code to use where and orWhere . Here's a workaround:
$product = Spire_models\Product ::where(function ($query) { $query->whereHas('uniqueItem.fulfillmentCenterUniqueItems', function ($query) { $query->where('id', 7089); }); }) ->orWhere(function ($query) { $query->whereHas('skus.uniqueItem.fulfillmentCenterUniqueItems', function ($query) { $query->where('id', 7412); }); }) ->get()->toArray();