CODEWITHSUNDEEP
symfonydoctrine-ormtwig
Faery
Faery

Reputation: 4650

How to sum with joined tables with Doctrine

I have 2 related entities - Category and Expense. Expense has a date - when the expense was made.

A Category entity has a one-to-many relationship with multiple Expense entities which themselves have a name(product) and a price property.

I want to calculate the sum of all expense->price properties for a single category for the current month. (I have working findExpensesForMonthAndCategory method)

Can you help me please?

Category

class Category
{
    protected $name;
    protected $expenses;

    public function __construct()
    {
        $this->expenses = new ArrayCollection();
    }
}

Expense

class Expense
{
    protected $category;
    protected $product;
    protected $price;
    protected $date;
}

Upvotes: 3

Views: 2210

Answers (1)

Nicolai Fröhlich
Nicolai Fröhlich

Reputation: 52493

There are actually 2 ways of solving this.

Solution 1

Add a getExpenseSum() Method to your Category entity...

class Category 
{

// ...

    public function getExpenseSum() 
    {    
        $sum = 0;
        foreach ($this->expenses as $expense) {
            $sum += $expense->getPrice();
        }

        return $sum;
     }

Now you can output the sum in twig this way:

     {{ category.expenseSum }}

Solution 2

create a method in your repository. 

// src/Vendor/YourBundle/Entity/ExpenseRepository 

public function getSumByCategory($category)
{
    $q = $this->createQueryBuilder('e')
        ->sum('e.price')
        ->where('e.category = :category')
        ->setParameter('category', $category)
        ->getQuery();

    return $q->getResult();
}

Upvotes: 5

Related Questions