CODEWITHSUNDEEP
phpsymfonydoctrine
Abdel5
Abdel5

Reputation: 1120

Symfony 2 - best approach for summing results of values in related entities

My code is aimed at getting items from database. Those item may have either debit values or credit values. In template I present all of the values but would like also to provide information about sum of debit and account values connected with the account ($account_id)

What would be your best practice how to do this?

Should I simply run another two SQL queries: first to SUM(value) where accountdebet = '.$account_id secound to SUM(value) where accountcredit = '.$account_id?

Will it be the right approach?

Controller:
    // Get items for acccount and paginate
            $rp_items = $this->getDoctrine()->getManager()->getRepository('AppBundle:Items');
            $query = $rp_items->createQueryBuilder('p')
            ->where('p.accountdebet = '.$account_id)
            ->orWhere('p.accountcredit = '.$account_id)
            ->getQuery();
            $rp_paginator  = $this->get('knp_paginator');
            $db_pagination = $rp_paginator->paginate($query,$this->get('request')->query->getInt('page', 1),10);
            // Render TPL
            return $this->render('AppBundle:Accounts:items.html.twig', array('pagination' => $db_pagination, 'account' => $account));

In twig:

 {% extends '::base.html.twig' %}

 {% block body %}
     <B>Item list for account {{ account.id }} </B><BR>

Account id: {{ account.id }}<BR>
Account marker: {{ account.marker }}<BR>
Account name: {{ account.name }}<BR>

<table class="table table-striped">
<tr>
{# sorting of properties based on query components #}
    <th>{{ knp_pagination_sortable(pagination, 'Id', 'a.id') }}</th>
    <th{% if pagination.isSorted('a.itemdate') %} class="sorted"{% endif %}>{{ knp_pagination_sortable(pagination, 'Date', 'a.itemdate') }}</th>
    <th>Document</th>
    <th>{{ knp_pagination_sortable(pagination, 'Marker', 'a.marker') }}</th>
    <th>Debit</th>
    <th>Credit</th>
</tr>

{# table body #}
{% for item in pagination %}
<tr>
    <td>{{ item.id }}</td>
    <TD>{{ item.itemdate|date('Y-m-d')}}</TD>
    <td><A HREF="{{url('app_documents_details', {'id': item.documentid.id})}}">{{ item.documentid.marker }}</A></td>
    <td>{{ item.marker }}</td>

    <TD>{% if item.accountdebet.id == account.id %}
    {{ item.itemvalue}}
    {% endif %}
    </TD>
    <TD>{% if item.accountcredit.id == account.id %}
    {{ item.itemvalue}}
    {% endif %}</TD>
</tr>
{% endfor %}
</table>
{# display navigation #}
<div class="pagination">
    {{ knp_pagination_render(pagination) }}
</div>
 {% endblock %}

Upvotes: 1

Views: 1556

Answers (1)

Frank B
Frank B

Reputation: 3697

You can solve it in many ways but i like to make an parent entity eg ItemsCollection that holds an arrayCollection of Items. Then i give the new entity some extra functionality e.g.

public function countItemValues() 
{
   $total = 0;

   foreach($this->items as $item)
   {
       $total += $item->getValue();
   }

   return $total;
}

Upvotes: 1

Related Questions