Level 88
What have you tried so far?
Jan 18, 2020
4
Level 1
How can I get data through Eloquent filtering multiple columns
I have 3 tables. stocks, products, purchase_items.
Stock Model:
protected $fillable = ['product_id', 'product_code_id', 'quantity'];
public function code()
{
return $this->belongsTo(ProductCode::class);
}
public function product()
{
return $this->belongsTo(Product::class);
}
PurchaseItem Model:
protected $fillable = ['product_id', 'product_code_id', 'quantity'];
public function code()
{
return $this->belongsTo(ProductCode::class);
}
public function product()
{
return $this->belongsTo(Product::class);
}
Product Model:
protected $fillable = ['id', 'name'];
public function purchaseItems()
{
return $this->hasMany(PurchaseItem::class);
}
I want to get data from Stocks table with today's purchased quantity which will filter by product_code, product_id
Level 1
$stocks = Stock::orderBy('stocks.updated_at', 'DESC')
->selectRaw('
stocks.product_id, stocks.code_id, purchase_items.quantity as todayIn,
stocks.quantity as availableQuantity,
product_codes.name as codeName, products.name as productName
')
->join('products', 'stocks.product_id', '=', 'products.id')
->join('product_codes', 'stocks.code_id', '=', 'product_codes.id')
->leftjoin('product_purchase_items', function (JoinClause $join){
$join->on('stocks.product_id', '=', 'purchase_items.product_id')
->on('stocks.code_id', '=', 'purchase_items.product_code_id')
->whereDate('purchase_items.created_at', date('Y-m-d'));
})
->paginate(25);
I tried this. but It seems so messy.
Level 122
Looks like you could do with a hasManyThrough relation from Stock to PurchaseItem via Product?
Level 1
I thought so, but I couldn't.
Please or to participate in this conversation.