CODEWITHSUNDEEP
mysqllaraveleloquent
owf
owf

Reputation: 253

How to multiply and group using laravel eloquent with relation

I'd like to export data from my database but have problems with multiplying and sum using laravel eloquent with relation

So i have 2 tables there (budgets, items)

Budget's:

// Table
+----+---------------+-----------------+------+-----+--------------------+
| id | delivery_plan | item_code       | curr | qty | price              |
+----+---------------+-----------------+------+-----+--------------------+
|  1 | 2022-08       | 201.0001        | IDR  |   1 |    2000.0000000000 |
|  2 | 2022-08       | 201.0001        | IDR  |   3 |    2000.0000000000 |
|  3 | 2022-07       | 201.9999        | IDR  |   2 |    2000.0000000000 |
+----+---------------+-----------------+------+-----+--------------------+

// Relation

public function item()
{
    return $this->belongsTo(Item::class, 'item_code', 'item_code');
}

Items :

// Table
+----+----------------+-----------+
| id | subgroup       | item_code |
+----+----------------+-----------+
|  1 | KOMPONEN MESIN | 201.0001  |
|  2 | EQUIPMENT LAIN | 201.9999  |
+----+----------------+-----------+

// Relation
public function budgets()
{
    return $this->hasMany(Budget::class, 'item_code', 'item_code');
}

So, the scenario is :

  1. Multiply the "qty" * "price" columns and name them as "total" like so
  2. Group them by "subgroup" column, which came from item() relationship
  3. Group them by "delivery_plan"

I prefer using eloquent because to minimize the complexity because i need that "whereHas" method This is what i've tried so far and isn't working :

$budgets = Budget::with('item', 'rate')->whereHas('period.term', function (Builder $builder) {
    $builder->where('name', '=', Session::get('term-budget'));
})->where('section', Session::get('section-budget'))->getQuery();

$result = $budgets->sum('price * qty')->get();

How can i achieve this ?

Upvotes: 0

Views: 645

Answers (1)

Vadim Sirbu
Vadim Sirbu

Reputation: 681

This can be solved by a join with SUM(), something like below (untested):

Budget::leftJoin('items', 'budgets.item_code', '=', 'items.item_code')
    ->addSelect('subgroup')
    ->addSelect('delivery_plan')
    ->addselect(\DB::raw('SUM(qty * price) as total'))
    ->groupBy('subgroup', 'delivery_plan')
    ->get();

Upvotes: 1

Related Questions