How to get items are not in a collection in Laravel Eloquent?

Question

In my Laravel 6.x project I have Product model, Warehouse and WarehouseProduct models.

In the Product I store the base information of my products. In the WarehouseProduct I store the stock amount informations about products in warehouse. Of course I have many warehouses with many products.

My Product looks like this:

class Product extends Model
{
    protected $fillable = [
        'name',
        'item_number',
        // ...
    ];
}

The Warehouse looks like this:

class Warehouse extends Model
{
    protected $fillable = [
        'name',
        'address',
        // ...
    ];

    public function products() {
        return $this->hasMany(WarehouseProduct::class);
    }

    public function missingProduct() {
        // here I need to return a Product collection which are not in this Warehouse or the
        // stored amount is 0
    }
}

Finally the WarehouseProduct looks like this:

class WarehouseProduct extends Model
{
    protected $fillable = [
        'product_id',
        'warehouse_id',
        'amount',
        // ...
    ];

    public function product() {
        return $this->belongsTo(Product::class, 'product_id');
    }

    public function warehouse() {
        return $this->belongsTo(Warehouse::class, 'warehouse_id');
    }

How can I get a Product collection which are not stored in a Warehouse or the amount is 0?

Answer 1

Something like this should work:

use App\Product;

public function missingProduct() {
    $excludedProducts = $this->products()->where('amount', '>', 0)->pluck('id');

    return Product::whereNotIn('id', $excludedProducts)->get();
}

Based on @KarolSobański's solution, when you add a warehouse_products relation to your product model:

use App\Product;
use Illuminate\Database\Eloquent\Builder;

public function missingProduct() {
    return Product::whereDoesntHave('warehouse_products', function (Builder $query) {
        $query->where('warehouse_id', $this->id);
    })->orWhereHas('warehouse_products', function (Builder $query) {
        $query->where('warehouse_id', $this->id);
        $query->where('amount', 0);
    })->get();
}