QueryBuilder / Doctrine选择join groupby [英] QueryBuilder/Doctrine Select join groupby

问题描述

所以最近我一直在想，也找不到一个解决方案，因为我缺乏开发与doctrine2和symfony查询生成器。

我有2表：
目标：id，user_id，target_value ...
储蓄：id，goal_id，金额

我需要做一个选择目标（我的表中的所有信息都来自目标表，除了我需要从每个目标的储蓄表中创建一个SUM（金额），所以我可以向用户显示他为了目标而节省了多少） p>

这是MySQL查询：

  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 
其中admin_goals.user_id = 1 
 group by admin_goals.id 
  

它返回我想要的，但我不知道如何使用原则或查询生成器来实现它，你能否以两种方式给我一个例子？

我非常感谢！

解决方案

我将假设您只需要这个字段，而不是您的 AdminGoals 实体。在你的 AdminGoalsRepository 你可以这样做：

  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'）//是这个实体？它将只是一个ID 
  - > join（'goal.adminSavings'，'saving'，Join :: WITH））
  - > where（$ qb-> expr（） - > ; $（$）
  - > groupBy（'goal.id'）
  - > setParameter（'user'，$ user）; 
 
 return $ qb-> getQuery（） - > getScalarResult（）; 
} 
  

请记住，返回对象将是行数组，每行是一个与上面的映射关键字相关联的数组。

编辑

更新问题后，我将更改我的建议功能，但要离开上述例如，如果其他人想看到差异。

首先，因为这是 AdminSavings 和 AdminGoals ，自定义查询应该在 AdminSavingsRepository （不像上面）。此外，由于您希望聚合字段，这将break您的某些数据获取。尝试保持尽可能多的OOP，当你不只是渲染模板。

  public function getSavingsByUser（User $ user）
 {
 $ qb = $ this-> createQueryBuilder （ '储蓄'）; 
 //现在我们可以使用expr（）函数
 $ qb-> select（'SUM（savings.value）AS savings_value'）
  - > addSelect（'goal.created '）
  - > addSelect（'goal.description'）
  - > addSelect（'goal.goalDate'）
  - > addSelect（'goal.value'）
  - > addSelect（'goal.budgetCat'）//这将只是一个ID 
  - > join（'savings.goal'，'goal'，Join :: WITH））
  - > where（$ qb-> expr（） - > eq（'goal.user'，'：user'））
  - > groupBy（'goal.id'）
  - > ; setParameter（'user'，$ user）; 
 
 return $ qb-> getQuery（） - > getScalarResult（）; 
} 
  





奖金

  public function FooAction（$ args）
 {
 $ em = $ this-> getDoctrine（） - > getManager（）; 
 $ user = $ this-> getUser（）; 
 //检查用户是否是用户等等取决于您的配置
 ... 
 
 $ saving = $ em-> getRepository（'AcmeBundle：AdminSavings'） - > getSavingsByUser（$用户）; 
 
 foreach（$ saving as $ row）{
 $ savings = $ row ['savings_value']; 
 $ goalId = $ row ['id']; 
 $ goalCreated = $ row ['created']; 
 [...] 
} 
 [...] 
} 
  

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 !

解决方案

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'];
        [...]
    }
    [...]
}

这篇关于QueryBuilder / Doctrine选择join groupby的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！