php - QueryBuilder/Doctrine Select join groupby -

so have been thinking , can't find solution yet problem since lack of development doctrine2 , symfony query builder.

i have 2 tables: goals: id,user_id,target_value... savings: id,goal_id,amount

and need make select goals (all informations in table goals table, except need make sum(amount) savings table on each goal, can show user how did saved goal)

this mysql query:

    select       admin_goals.created,     admin_goals.description,     admin_goals.goal_date,     admin_goals.value,     admin_goals.budget_categ,     sum(admin_savings.value)       admin_goals  inner join admin_savings on admin_savings.goal_id=admin_goals.id      admin_goals.user_id=1      group admin_goals.id 

it returns want have no idea how implement doctrine or query builder, can please show me example in both ways? highly appreciate !

i going assume need fields , not admingoals entity. on admingoalsrepository can this:

public function getgoalsbyuser(user $user)  {     $qb = $this->createquerybuilder('goal');     $qb->select('sum(savings.value) savings_value')        ->addselect('goal.created')        ->addselect('goal.description')        ->addselect('goal.goaldate')        ->addselect('goal.value')        ->addselect('goal.budgetcat') //is entity? 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 return object array of rows, each row associated array keys mappings above.

edit

after updating question, going change suggested function going leave above example if other people see difference.

first things first, since unidirectional manytoone between adminsavings , admingoals, custom query should in adminsavingsrepository (not above). also, since want aggregated field "break" of data fetching. try stay oop when not rendering templates.

public function getsavingsbyuser(user $user) {     $qb = $this->createquerybuilder('savings');     //now can use expr() function     $qb->select('sum(savings.value) savings_value')        ->addselect('goal.created')        ->addselect('goal.description')        ->addselect('goal.goaldate')        ->addselect('goal.value')        ->addselect('goal.budgetcat') //this 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 user etc depends on config     ...      $savings = $em->getrepository('acmebundle:adminsavings')->getsavingsbyuser($user);      foreach($savings $row) {         $savings = $row['savings_value'];         $goalid =  $row['id'];           $goalcreated = $row['created'];         [...]     }     [...] }