this is a question that continues another topic :
QueryBuilder/Doctrine Select join groupby
Please open it so you can see there the database schema.
My problem is:
I have a goal and a goal has many savings.
That means that if you want to save money for a goal you will add some savings, so the savings are linked to the goal by id.
In that topic someone talked about a unidirectional link between savings and goals, ( I have goal_id in the savings table, but no saving_id in the goal table).
What I want to do is show a table with a Goal information and append to it a collected value(which is not in the database) this value is the sum of all saving values linked to a goal:
ex :
Goals:
id: 1
goal name: Goal1
....
Savings:
id: 1
amount:10
goal_id: 1
id:2
amount:20
goal_id: 1
When I query the database I will get a table with the goal information and a field Amount Collected: 30 in this case.
I initially managed to make the query to get me this:
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
But I wanted to do the symfony way so I used query builder, made a repository and didn't have the same result.
The only link between Goals and Savings is the goal_id from the Savings table, that means that using query builder I have to start from the Savings table.. because from the Goals table I have no foreign_key for savings.
But now the problem is that because I start from the Savings table, when displaying the Goals I get a empty result, thats because initially there are no Savings for a Goal.
First you create the Goal and then the Savings. But because there is no record in the Savings table(i start from) I don't get nothing from the Goals table because I have a join on these two tables.
$qb = $this->createQueryBuilder('admin_savings');
$qb->select('SUM(admin_savings.value) AS savingValue')
->addSelect('admin_goals.id AS id')
->addSelect('admin_goals.created')
->addSelect('admin_goals.description')
->addSelect('admin_goals.goal_date')
->addSelect('admin_goals.value')
->addSelect('admin_budget.name') // daca vrei id atunci pune identity(admin_goals.categ_id) admin_budget.name, x.name
->join('admin_savings.goal_id', 'admin_goals', Join::WITH)
->join('admin_goals.budget_categ', 'admin_budget', Join::WITH) //parametru 2 e numele de referinta in selectul mare ex: admin_budget, putea fi x
->where($qb->expr()->eq('admin_goals.user_id', ':user'))
->groupBy('admin_goals.id')
->setParameter('user', $user);
I understand that this is not possible so i tried another approach, why not I query normally the Goals entity because that is what I want to see..
$repository = $this->getDoctrine()->getRepository('AppBundle:Goal');
$goals = $repository->findBy(array('user_id' => $userId));
and I made a function in my SavingsRepository :
public function getSavingSumByGoal($goalId){
$qb = $this->createQueryBuilder('saving');
$qb->select('SUM(saving.value) AS savingValue')
->where($qb->expr()->eq('saving.goal_id', ':goal'))
->setParameter('goal', $goalId);
return $qb->getQuery()->getScalarResult();
}
So now I have the Goals entity with all its information, but not the Amount Sum Collected from the Savings, I need to add that somehow to my $goals object. This is in my controller
foreach($goals as $goal_item){
$savingSum=$SavingRepository->getSavingSumByGoal($goal_item->getId());
if($savingSum){
$goal_item->savingSum=$savingSum;
}else{
$goal_item->savingSum=0.00;
}
}
$goal_item->savingSum is a fictional property, it doesn't exists in the $goal object, I made this as an example that I want to insert that savingSum somehow in the $goal object.
I don't want to create another field in the entity and set that, because then I would have a empty column in the database, and I don't want that.. I want to keep the structure of my tables and entities, and only add that sum of all savings amount for a goal in the goals table.
But it doens't work. I was thinking in the foreach in my twig to execute somehow my function, but I don't think that is a good idea.
{% for goal in goals %}
<tr style="">
<td>{{ goal.created|date('Y/m/d') }}</td>
<td>{{ goal.description|e }}</td>
<td>{{ goal.value|e }}</td>
<td>{{ goal.goal_date|date('Y/m/d') }}</td>
<td>{{ goal.name|e }}</td>
<td>{{ savingRepo->getSavingSumByGoal(goal.id)|e }} </td>
</td>
</tr>
{% endfor %}
This is a bad practice and it doesn`t work in twig, but I know people used approach in Zend and other frameworks (after you made the foreach() you run a function of your on the current objects(from foreach) id and show something based on that)
If you have better ideas on how to do this the symfony way .. I can always run the rawquery and it would work, but that is not that I want to do.. there must be a way to do this in a OOP way..please help.
Thank you!
I don't want to create another field in the entity and set that, because then I would have a empty column in the database
You don't have to map a field to database, it can be normal class field