Sum by cases using just Eloquent

Question

I have a model called Purchases that has a many-to-many relationship with Product model. I created a model to manage the table in the many-to-many relationship called PurchasedProduct. This model is related to an other model called Currency.

Purchases table: Id, number, created_at, updated_at.
Product_table: Id, description, created_at, updated_at.
Currency table: Id, code, name, ratio.
Product_Purchase table: Id, purchase_id, product_id, currency_id, quantity, price.

An User can purchase several Products then, he can pay in many Currencies. To manage every transaction, I want to sum the total (quantity * price) by currency. I've tried to do it with an accessor but all I can do is sum the product of quantity * price without consider the currency.

public function getTotalAttribute($value)
{
    $transformers = $this->transformers();
    return $transformers->sum(\DB::raw('quantity * price'));
}`

You should start here by writing the exact raw MySQL query which generates the output you want. Then, worry about transforming that to Eloquent/PHP code. — Tim Biegeleisen
@TimBiegeleisen sure, I have it but I'm using just laravel to looking for the results — Golinmarq
Then you should include that query. I think it would help you get an answer here faster. — Tim Biegeleisen

hossamGamal hossamGamal · Accepted Answer · 2018-10-14T16:20:17

you can use groupBy

public function getTotalAttribute($value)
{
    $transformers = $this->transformers();
    return $transformers->groupBy('currency_id')
    ->selectRaw('sum(quantity * price) as sum, currency_id')
    ->get();
}

Sum by cases using just Eloquent

1 Answers