所以最近我一直在思考,但由于我缺乏对doctrine2和symfony查询生成器的开发,因此无法解决该问题。
我有2个表格:目标:id,user_id,target_value ...储蓄:id,goal_id,金额
而且我需要从目标中进行选择(表中的所有信息均来自目标表,除了我需要从每个目标的储蓄表中得出SUM(金额),以便我可以向用户显示他为目标而保存)
这是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
where 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') //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();
}
请记住,返回对象将是一个行数组,每一行都是一个具有如上述映射之类的键的关联数组。
更新问题后,我将更改建议的功能,但是如果其他人希望看到不同之处,请离开上面的示例。
首先,由于这是AdminSavings
和之间的单向ManyToOne AdminGoals
,因此自定义查询应位于AdminSavingsRepository
(与上面不同)。同样,由于您想要一个聚合字段,这将“破坏”您的某些数据获取。当您不只是渲染模板时,尝试保持尽可能多的OOP。
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();
}
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'];
[...]
}
[...]
}
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句