Laravel 9 withCount with conditions in both tables

Hi, I was wondering if its possible or not to use withCount, but count only those entries that match conditions in both tables.

I have 2 tables inventories and inventory_items.

inventory:

id
title
user_id
is_deleted

inventory_items:

id
product_id
inventory_id
expiration_date

Now I need a couple of things: First : Count how many products are in each inventory. That is achieved pretty easily with hasMany relationship set in Inventory modal. And then in controller getting the count with withCount

Inventory modal:

public function inventoryItems()
    {
        return $this->hasMany(InventoryItem::class);
    }

Inventory controller:

$data['itemCount'] = Inventory::where('user_id', Auth::id())->where('deleted', null)->withCount([InventoryItem]);

Second: I need to count how many of those items are expired in each inventory. So here I come to a halt. Because I can't figure out how can I put a where clause that would exclude deleted inventories from Inventories table AND exclude non-expired items from inventory_items table

Sinnbeck

3 years ago

Best Answer

Level 102

You can give withCount a callback to add a where clause

Example

Inventory::where('user_id', Auth::id())->where('deleted', null)->withCount(['inventoryItems' => function($query) {
    $query->where('status', 32);
}])->get();

You can also give it a name, if you have multiple


->withCount(['inventoryItems as disabled_inventory_count' => function($query) {
    $query->where('status', 32);
}])

1 like

Sinnbeck

3 years ago

Level 102

@StrugglingFish to allow other people to help, I would suggest you make a fresh thread showing the relationship between a user and inventory items :)

1 like

StrugglingFish

3 years ago

Level 1

Works like a charm!

Please or to participate in this conversation.