How to add model's relationed condition to Eloquent query in Laravel 6.x?

Question

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

In the Product I store the base information of my products. In ProductCategory model I store the category informations of 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',
        // ...
    ];

    public function categories() {
        return $this->belongsToMany(ProductCategory::class, 'product_category_products',
            'product_id', 'product_category_id');
    }
}

The ProductCategory looks like this:

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


    public function products() {
        return $this->belongsToMany(Product::class, 'product_category_products',
            'product_category_id', 'product_id');
    }
}

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

I have this query now:

$query = WarehouseProduct::select([
    'product_id',
    'warehouse_id',
    DB::raw('SUM(free_amount)'),
    DB::raw('SUM(booked_amount)'),
    // ...
]);

if (isset($request->warehouse_id)) {
    $query->where([['warehouse_id', '=', $request->warehouse_id]]);
}

if (isset($request->product_category_id)) {
    // ???
}

How can I add a where condition to the query what said: products from this category?

Answer 1

You can query the Relationship Existence. As it is a relationship through another model (Product) you could reduce the query if you defined that Has Many Through relationship, but I think that this will be enough for this particular query.

$warehouse_id = $request->warehouse_id;
$product_category_id = $request->product_category_id;

$query = WarehouseProduct::select([
    'product_id',
    'warehouse_id',
    DB::raw('SUM(free_amount)'),
    DB::raw('SUM(booked_amount)'),
    // ...
])
->when($warehouse_id, function ($query) use ($warehouse_id) {
    $query->where('warehouse_id', $warehouse_id);
})
->when($product_category_id, function ($query) use ($product_category_id) {
    $query->whereHas('product', function ($que) use ($product_category_id) {
        $que->whereHas('categories', function ($q) use ($product_category_id) {
            $q->where('id', $product_category_id);
        })
    })
});

$results = $query->get();

Note that I am using the when method for the conditional clauses, but you can continue with the ifs as you was doing.