CODEWITHSUNDEEP
phplaraveleloquenteloquent-relationship
netdjw
netdjw

Reputation: 6007

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

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?

Upvotes: 2

Views: 6009

Answers (2)

Remul
Remul

Reputation: 8252

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();
}

Upvotes: 1

Karol Sobański
Karol Sobański

Reputation: 434

The shortest answer might be similar to this:

Product::doesntHave('warehouse_products')
       ->orWhereHas('warehouse_products', function (Builder $query) {
           $query->where('amount', '=', 0)
       })->get();

Although I am not sure if the above works.

But the following longer query certainly resolves the issue: 

Product::where(function ($query) {
    $query->doesntHave('warehouse_products');
})->orWhere(function ($query) {
    $query->whereHas('warehouse_products', function (Builder $query) {
       $query->where('amount', '=', 0);
    });
})->get();

Upvotes: 1

Related Questions