Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

ehsanquddusi's avatar

Help in converting query to eloquent

I need some help in converting this query into eloquent equivalent. The model in which the query is to be executed is named as Inventory. This field should be appended to the column list.

What I am trying to achieve is, say, I already have columns a & b in Inventory and I want to append avl_quantity to it.

Writing Inventory::withAvailableQuantity()->get() should fetch the records from Inventory table with avl_quantity appended to it. I am actually trying to add this extra column via scope to hook on the underlying query builder of the model so that I can run other scopeMethods() on it. e.g,

Inventory::ofItem(2)->withAvailableQuantity()->first()

Here is the full query

SELECT *, (
    ei.quantity + 
    COALESCE((SELECT SUM(quantity) FROM ecom_inventory ei2 
        WHERE ei2.purchase_item_id = ei.movement_item_id), 0)
) AS `avl_quantity` FROM ecom_inventory ei WHERE ei.movement_item_type = 'Ecommerce\PurchaseItem' GROUP BY ei.movement_item_id

And here is the sub query to fetch only quantity which I want to pass on using $this->addSelect()

(ei.quantity + 
    COALESCE((SELECT SUM(quantity) FROM ecom_inventory ei2 
        WHERE ei2.purchase_item_id = ei.movement_item_id), 0)
) AS `avl_quantity`

Here ei is the base table of model.

Please help me in converting this query using query builder.

0 likes
9 replies
SilenceBringer's avatar

@ehsanquddusi

Inventory::select([
    'movement_item_type',
    'movement_item_id',
    'item_id',
    'purchase_item_id',
    \DB::raw('COALESCE((SELECT SUM(quantity) FROM ecom_inventory ei2 WHERE ei2.purchase_item_id = ei.movement_item_id), 0)) AS `quantity`')
])
    ->where('ei.movement_item_type', \Ecommerce\PurchaseItem::class)
    ->groupBy('ei.movement_item_id')
    ->get();
2 likes
orest's avatar

Maybe you can add query scope

public function scopeOfItem($query, $value)
{
     return $query->where('item_id', $value);
}

public function scopeWithAvailableQuantity($query)
{
    return $query->selectRaw('COALESCE((SELECT SUM(quantity) 
    FROM ecom_inventory ei2 WHERE ei2.purchase_item_id = ei.movement_item_id), 0)) 
    AS `quantity`'
    )->where('ei.movement_item_type', \Ecommerce\PurchaseItem::class)
        ->groupBy('ei.movement_item_id');
}

Inventory::ofItem(2)->withAvailableQuantity()->get();
1 like
ehsanquddusi's avatar

Thank you for the answer. This is the approach I am following. How can I refer to base model columns in this query. Like, instead of using ei.movement_item_id I want to use movement_item_id from the base query.

Something like

public function scopeWithAvailableQuantity($query)
{
    return $query->selectRaw('COALESCE((SELECT SUM(quantity) 
    FROM ecom_inventory ei2 WHERE ei2.purchase_item_id = movement_item_id), 0)) 
    AS `quantity`'
    )->where('movement_item_type', \Ecommerce\PurchaseItem::class)
        ->groupBy('movement_item_id');
}

where movement_item_id & movement_item_type refers to model`s columns.

SilenceBringer's avatar

@ehsanquddusi model has getTable method, you can use it

public function scopeWithAvailableQuantity($query)
{
    return $query->selectRaw('COALESCE((SELECT SUM(quantity) 
    FROM ecom_inventory ei2 WHERE ei2.purchase_item_id = ' . $this->getTable() . '.movement_item_id), 0)) 
    AS `quantity`'
    )->where($this->getTable() . '.movement_item_type', \Ecommerce\PurchaseItem::class)
        ->groupBy($this->getTable() . '.movement_item_id');
}
1 like
ehsanquddusi's avatar

Thank you. I have been able to achieve this

public function scopeWithAvailableQuantity($query)
    {
        return $query->addSelect(
            DB::raw('(' . $this->getTable() . '.quantity + COALESCE((SELECT SUM(quantity)
                FROM ecom_inventory ei2 WHERE ei2.purchase_item_id = ' . $this->getTable() . '.movement_item_id), 0))
                AS `avl_quantity`'
                )
            )->where($this->getTable() . '.movement_item_type', PurchaseItem::class)
                    ->groupBy([$this->getTable() . '.movement_item_id', $this->getTable() . '.item_id']);
    }

But the problem is that the get() method returns only this column while as I am using addSelect(). How can I append this attribute apart from the existing model attributes.

I know that I can append an attribute to model, but I would like to go with scope thing only.

ehsanquddusi's avatar
ehsanquddusi
OP
4 years ago
Best Answer
Level 2

This is my final solution to it.

public function scopeWithAvailableQuantity($query)
{
    return $query->select(
            DB::raw($this->getTable() . '.*, (' . $this->getTable() . '.quantity + COALESCE((SELECT SUM(quantity)
                FROM ecom_inventory ei2 WHERE ei2.purchase_item_id = ' . $this->getTable() . '.movement_item_id), 0))
                AS `avl_quantity`'
                )
        )->where($this->getTable() . '.movement_item_type', PurchaseItem::class)
        ->groupBy([$this->getTable() . '.movement_item_id', $this->getTable() . '.item_id']);
}
1 like
orest's avatar

If the initial columns are missing you can use

$query->select()->addSelect(….)

Please or to participate in this conversation.