QueryBuilder/Doctrine Select join groupby

Question

So recently I have been thinking and can't find a solution yet to this problem since my lack of development with doctrine2 and symfony query builder.

I have 2 tables: Goals: id,user_id,target_value... Savings: id,goal_id,amount

And I need to make a select from goals (all the informations in my table are from the goals table, except that I need to make a SUM(amount) from the savings table on each goal, so I can show the user how much did he saved for his goal)

This is the MySQL query:

    select 

    admin_goals.created,
    admin_goals.description,
    admin_goals.goal_date,
    admin_goals.value,
    admin_goals.budget_categ,
    sum(admin_savings.value) 

    from admin_goals 
inner join admin_savings on admin_savings.goal_id=admin_goals.id 
    where admin_goals.user_id=1 
    group by admin_goals.id

It returns what I want but I have no idea how to implement it with doctrine or query builder, can you please show me an example in both ways? I highly appreciate it !

Answer 1

I am going to assume you need this fields only and not your AdminGoals entity. On your AdminGoalsRepository you can do something like this:

public function getGoalsByUser(User $user) 
{
    $qb = $this->createQueryBuilder('goal');
    $qb->select('SUM(savings.value) AS savings_value')
       ->addSelect('goal.created')
       ->addSelect('goal.description')
       ->addSelect('goal.goalDate')
       ->addSelect('goal.value')
       ->addSelect('goal.budgetCat') //is this an entity? it will be just an ID
       ->join('goal.adminSavings', 'savings', Join::WITH))
       ->where($qb->expr()->eq('goal.user', ':user'))
       ->groupBy('goal.id')
       ->setParameter('user', $user);

    return $qb->getQuery()->getScalarResult();
}

Keep in mind that the return object will be an array of rows, each row is an associated array with keys like the mappings above.

Edit

After updating the question, I am going to change my suggested function but going to leave the above example if other people would like to see the difference.

First things first, since this is a unidirectional ManyToOne between AdminSavings and AdminGoals, the custom query should be in AdminSavingsRepository (not like above). Also, since you want an aggregated field this will "break" some of your data fetching. Try to stay as much OOP when you are not just rendering templates.

public function getSavingsByUser(User $user)
{
    $qb = $this->createQueryBuilder('savings');
    //now we can use the expr() function
    $qb->select('SUM(savings.value) AS savings_value')
       ->addSelect('goal.created')
       ->addSelect('goal.description')
       ->addSelect('goal.goalDate')
       ->addSelect('goal.value')
       ->addSelect('goal.budgetCat') //this will be just an ID
       ->join('savings.goal', 'goal', Join::WITH))
       ->where($qb->expr()->eq('goal.user', ':user'))
       ->groupBy('goal.id')
       ->setParameter('user', $user);

       return $qb->getQuery()->getScalarResult();
}

Bonus

public function FooAction($args) 
{
    $em = $this->getDoctrine()->getManager();
    $user = $this->getUser();
    //check if user is User etc depends on your config
    ...

    $savings = $em->getRepository('AcmeBundle:AdminSavings')->getSavingsByUser($user);

    foreach($savings as $row) {
        $savings = $row['savings_value'];
        $goalId =  $row['id'];  
        $goalCreated = $row['created'];
        [...]
    }
    [...]
}

Answer 2

If you use createQuery(), then you can do something like this:

    $dqlStr = <<<"DSQL"
select 
admin_goals.created,
admin_goals.description,
admin_goals.goal_date,
admin_goals.value,
admin_goals.budget_categ,
sum(admin_savings.value)
from admin_goals 
inner join admin_savings on admin_savings.goal_id=admin_goals.id 
where admin_goals.user_id=1 
group by admin_goals.id
DSQL;

    $em = $this->getDoctrine()->getManager();
    $query = $em->createQuery($dqlStr);
    $query->getResult();

On the other hand, if you would like to use createQueryBuilder(), you can check this link: http://inchoo.net/dev-talk/symfony2-dbal-querybuilder/