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?

Do you mean one specific Warehouse instance or any? – Karol Sobański May 19 '20 at 11:51 — Karol Sobański, May 19 '20 at 11:51

Remul · Accepted Answer · 2020-05-19T13:08:44.417

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

edited May 19 '20 at 13:08

answered May 19 '20 at 11:54

Remul

7,874
1
13
30

This actually is not a good solution - you make two database requests instead of one. You should use relations in your query to avoid such situation. – Karol Sobański May 19 '20 at 12:38
@KarolSobański I could not come up with a solution that uses the normal relationships laravel provides, so I don't think two queries are bad in this case, but could you add a solution that uses normal relationships, would be interesting to see. – Remul May 19 '20 at 12:44
added answer. At least one of proposed solutions should work – Karol Sobański May 19 '20 at 12:53
Thanks, first code is works fine, the second one not. – netdjw May 19 '20 at 15:28

Karol Sobański · Answer 2 · 2020-05-19T13:04:34.183

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

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

2 Answers2