Issue
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?
Solution
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.
Answered By - porloscerros Ψ Answer Checked By - Robin (PHPFixing Admin)
Posts
