Totals Sum/avg/etc in footer and/or header

I dont know if this is a new feature or already exists, but would be great in the header or footer of the grid we can insert a row with totals / averages for each column.

Something like this:

Customer     Invoice(count)    Ammount(sum)
Jhon         2                 1000
Mary         4                 1800
-------------------------------------------
Total        6                 2800

Thanks.

I think you can do it by overriding template blocks. (each grid_column block to count or sum and grid_rows to add a new row with these values)

If the grid has multiple pages how i can handle it? I have all the rows?

Ah, you want an AVG on all rows not only the visible rows ?

Exactly, i think we need a mechanism at column level, configured in entity or in the controller for that.
Like this:

@Grid\Column(title="Field", aggregateFunction="avg")

and/or

$footerRow = $grid->createFooterRow();
$footerRow->addCell('field', 'avg');
$grid->addFooterRow($footerRow);

What do you think?

Meanwhile there is a way to retrieve the QueryBuilder's grid in the controller to perform a extra query?

The problem is that we have to do a separate query to retrieve these informations.
In the GRID::manipulateQuery() method, you have access to the current query before its execution but you are here in a callback. I don't know if we can use referenced variable in callback or store values in the global scope of PHP.

<?php
...
$grid->setSource($source);
$grid->manipulateQuery(function () {
     // Clone the query and remove the 'limit' part and put your own 'select' part with 'avg' or 'count'
     // store somehow your query of the result of the query
});
...
$grid->isReadyForRedirect(); // The manipulateQuery callback is called in this function
...
$grid->getGridResponse();

Note: GRID::manipulateQuery() method returns the grid.

And in a template, override the grid_rows blocks to add your row with your data.

@Abhoryo

@Abhoryo thanks for the explanation.

This is my code in the action:

    /* @var $queryBuilder \Doctrine\ORM\QueryBuilder */
    $queryBuilder = null;

    $source->manipulateQuery(function ($qb) use(&$queryBuilder) {
           $queryBuilder = clone $qb;
    });

    if($grid->isReadyForRedirect()) {
        $result = NULL;
    } else {
        $queryBuilder->select('SUM(_a.ammount) AS totalAmmount');
        $queryBuilder->setMaxResults(NULL);
        $result = current($queryBuilder->getQuery()->execute());
    }

    return $response = $grid->getGridResponse('WebFactorySaleBundle:PlannedCharge:index.html.twig', array('totals' => $result));

My template:

{{ grid(grid, 'WebFactorySaleBundle:PlannedCharge:gridWithTotals.html.twig', null, {'totals':totals}) }}

And my custom template (simple and fast, but ugly):

{% extends 'APYDataGridBundle::blocks.html.twig' %}

{% block grid_rows %}

{{ parent() }}

{% if(grid.rows) %}
<tfoot>
    <tr>
        <td></td>
        <td></td>
        <td >{{ totals.totalAmmount }}</td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
</tfoot>
{% endif %}
{% endblock grid_rows %}

I think we need a better solution, is very common see grids with totalized rows.

This solution isn't so ugly :p

I can't work on this feature before september.

Any news about this?

Not at all.

Hi,

i'm interessting by this enhancement too.
Have you plan a date to work on it ?

Not really, sorry.

Hi,

I need to have this "aggregate line" on grids in my current projet, so i begin to work on a solution working with annotations.

Here is my first try, probably need some improvements (sorry for french comments!!).

Hope this will help someone :)

My custom source classe (extend existing Source\Entity)

<?php
/**
 * Surcharge de la source Entity standard pour y rajouter la notion de ligne avec des fonctions d'agrégation DQL
 * Rend possible l'utilisation des propriété "aggregateFunction" et "aggregateName"
 *  dans les annotations
 */
namespace Acme\DemoBundle\Classes;

use APY\DataGridBundle\Grid\Source\Entity;

class EntityWithAggregateRow extends Entity
{
    /**
     * @var array Tableau des résultats et nom des fonctions d'agregation
     */
    protected $aggregateResult;

    /**
     * Indicateur pour ignorer (ou pas) les filtres appliqués sur la grid
     * @var bool
     */
    protected $ignoreFilter = false;

    /**
     * Getter $agregateResult
     */
    public function getAggregateResult()
    {
        return $this->aggregateResult;
    }

    /**
     * Getter $ignoreFilter
     */
    public function isIgnoreFilter()
    {
        return $this->ignoreFilter;
    }

    /**
     * Setter $ignoreFilter
     * @param boolean $ignoreFilter
     */
    public function setIgnoreFilter( $ignoreFilter )
    {
        $this->ignoreFilter = $ignoreFilter;
    }

    /**
     * @param \Doctrine\ODM\MongoDB\Query\Builder $queryBuilder
     */
    public function prepareQuery($queryBuilder)
    {
        if( $this->isIgnoreFilter() )
        {
            //Si on doit ignorer les filtres, on utilise la query source pour constuire notre nouvelle requête
            $aggregateQueryBuilder = clone $this->querySelectfromSource;
        }else{
            //Sinon, on utilise la requête courante, mais sans les clauses LIMIT et OFFSET
            $aggregateQueryBuilder = clone $queryBuilder;
            $aggregateQueryBuilder->setMaxResults(NULL);
            $aggregateQueryBuilder->setFirstResult(NULL);
        }

        $this->prepareAgregateQuery($aggregateQueryBuilder);
        parent::prepareQuery($queryBuilder);
    }

    /**
     * Construit et execute la requete pour les fonctions DQL a appeler sur l'ensemble de la grid
     * 
     * @param \Doctrine\ODM\MongoDB\Query\Builder $aggregateQueryBuilder
     */
    private function prepareAgregateQuery( $aggregateQueryBuilder )
    {
        // Purge selects
        $aggregateQueryBuilder->select('1');

        $this->aggregateResult = array();

        $fields = $this->metadata->getFields();
        foreach( $fields as $field )
        {
            $meta = $this->metadata->getFieldMapping( $field );
            if ( isset( $meta['aggregateFunction'] ) )
            {
                $this->aggregateResult[$field]['name'] = isset( $meta['aggregateName'] ) ? $meta['aggregateName'] : '' ;
                $aggregateQueryBuilder->addSelect( $meta['aggregateFunction'] .'(' . $this->getTableAlias() . '.' . $field .') AS ' . $field );
            }
        }

        // Appel du parent::prepareQuery avec ce query builder pour que le manipulateQuery s'applique aussi sur les fonctions aggregate
        parent::prepareQuery($aggregateQueryBuilder);

        $resultSet = $aggregateQueryBuilder->getQuery()->getSingleResult();
        foreach ($this->aggregateResult as $field => $result){
            $this->aggregateResult[ $field ]['value'] = $resultSet[ $field ]; 
        }
    }
}

The modified parts of blocks.html.twig :

{# ------------------------- grid ------------------------ #}
{% block grid %}
<div class="grid">
{% if grid.totalCount > 0 or grid.isFiltered or grid.noDataMessage is sameas(false) %}
    <form id="{{ grid.hash }}" action="{{ grid.routeUrl }}" method="post">
        <div class="grid_header">
        {% if grid.massActions|length > 0 %}
            {{ grid_actions(grid) }}
        {% endif %}
        </div>
        <div class="grid_body">
        <table>
        {% if grid.isTitleSectionVisible %}
            {{ grid_titles(grid) }}
        {% endif %}
        {% if grid.isFilterSectionVisible %}
            {{ grid_filters(grid) }}
        {% endif %}
        {{ grid_rows(grid) }}
        {{ grid_aggregate_row(grid) }} {# <------------------------- add a new row  ------------------------ #}

        </table>
        </div>
        <div class="grid_footer">
        {% if grid.isPagerSectionVisible %}
            {{ grid_pager(grid) }}
        {% endif %}
        {% if grid.exports|length > 0 %}
            {{ grid_exports(grid) }}
        {% endif %}
        {% if grid.tweaks|length > 0 %}
            {{ grid_tweaks(grid) }}
        {% endif %}
        </div>
        {% if withjs %}
            {{ grid_scripts(grid) }}
        {% endif %}
    </form>
{% else %}
    {{ grid_no_data(grid) }}
{% endif %}
</div>
{% endblock grid %}

{# ------------------ grid_agregate_row ---------------------- #}
{% block grid_aggregate_row %}
    <tr>
        {% for column in grid.columns %}
            {% if column.visible(grid.isReadyForExport) %}
                <td class="grid-column-{{ column.renderBlockId }}{% if column.align != 'left'%} align-{{ column.align }}{% endif %}{% if loop.last %} last-column{% endif %} ">
                    {% if attribute( grid.source.aggregateResult, column.id ) is defined  %}
                        {{  attribute( grid.source.aggregateResult, column.id ).name }}
                        {{  attribute( grid.source.aggregateResult, column.id ).value }}
                    {% endif %}
                </td>
            {% endif %}
        {% endfor %}
    </tr>
{% endblock grid_aggregate_row %}

In the Doctrine entity, you can now use "aggregateFunction" et "aggregateName" in you GRID annotations.

/**
 * @ORM\Table()
 * @ORM\Entity
 */
class TestEntity
{
...
...

    /**
     * @ORM\Column(name="nombre", type="integer") 
     * @GRID\Column(title="Nombre", field="nombre", aggregateFunction="sum", aggregateName="Total", groups="front")
     */
    private $nombre;

In the controller, just use the new class as source instead of Entity.

use Acme\DemoBundle\Classes\EntityWithAggregateRow as GridEntity;
...
...
public function indexAction()
    {
        $source = new GridEntity('AcmeModelBundle:TestEntity', 'front');
        $source->setIgnoreFilter( true );// here, i need to ignore grid's filter in my sum

Julien

Sounds promising.

I think that could be a problem to call twice the prepareQuery callback. I think it's better to call this function once with the whole query (with your arggregate functions).

We should probably add the thead, tbody and tfoot tag to differentiate each part of the grid and apply easily css style on it.

Else setIgnoreFilter() function isn't really well named for what it do.

Thx for the feedback !

Indeed, the prepareQuery callback is usefull only when we want to base aggregate function on "querySelectfromSource" so it need a fix (as the ignoreFilter property).

Maybe i'll implement an manipulateAggregateQuery() method for more flexibility.

You right this has nothing to do with the manipulatequery. So you can add a manipulateAggregateQuery callback

Hi,
is there any news about this feature?
Would be nice if I could use the functionality from the bundle instead of building my own.

#552

Why was this issue closed? This issue is not the same as #552.
The issue is about a summary row below the datagrid, not another column from the entity.

It's releated i thing. I'm trying to handle with 90 issue. We need to group some of them. At version 3. this will be implemented. If you have time, purpose a PR. We may include it to 2.3

Navigation Menu

Repository navigation

Totals Sum/avg/etc in footer and/or header #207

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Totals Sum/avg/etc in footer and/or header #207

Description

Activity

Abhoryo commented on Jul 16, 2012

listeroabsoluto commented on Jul 16, 2012

Abhoryo commented on Jul 16, 2012

listeroabsoluto commented on Jul 16, 2012

listeroabsoluto commented on Jul 16, 2012

Abhoryo commented on Jul 17, 2012

clubdesarrolladores commented on Jul 18, 2012

Abhoryo commented on Jul 18, 2012

clubdesarrolladores commented on Sep 27, 2012

Abhoryo commented on Sep 27, 2012

xbegault commented on Mar 1, 2013

Abhoryo commented on Mar 1, 2013

j-guyon commented on Nov 6, 2013

Abhoryo commented on Nov 6, 2013

j-guyon commented on Nov 7, 2013

Abhoryo commented on Nov 8, 2013

Fuujin commented on Apr 28, 2015

hmert commented on Dec 13, 2015

Fuujin commented on Dec 16, 2015

hmert commented on Dec 16, 2015

Metadata

Metadata

Assignees

Labels

Type

Projects

Milestone

Relationships

Development

Issue actions