Perform where clause inside the Pivot Table on the whereHas

I have encountered an issue which I cannot perform where clause on the pivot table inside the whereHas eloquent function.

my expected query is

SELECT product.*,product_item.*
FROM product LEFT JOIN product_item ON 
product.id = product_item.product_id AND product_item.item_id = ?
WHERE product_item.product_id is null;

Currently, I have 2 laravel eloquent mode such as Product and Item as well as 1 pivot table which called product_items.

Product model
model attribute: id, name, status, created_at

**relation**
public function items(){
      return $this->belongsToMany(Item::class,'product_items');
}


Item model
model attribute: item_id, item_name, item_sku

Product_Item pivot table
table attribute: product_id, item_id, dt_added

I have a function called getProductThatIsNotOccupiedByItem() inside the product model as below shown:

public function getProductThatIsNotOccupiedByItem(
      return $query->when($id,function($query) use($id){
           $query->where(function($query) use ($id){
                $query->whereHas('item',function($q) use ($id){
                    //the problem here I cant track back pivot table
                });
            });
       });
}

In short, I need to pull the products that have not been occupied by items yet from the product side, if the items is not be occupied by any products, just print all the product by using the same whereHas function.

I really hope someone can help me convert the expected SQL query into the eloquent whereHas format because I have been stucking this function for more than a week.

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/3CGGr7i
via IFTTT