Sonata Admin Bundle: show total count of collection on list view

Question

Is there any way to show total count of collection on list view? Imagine that there is a user that can have many links. How can I show total links count on list view?

Answer 1

Show field it is quite easy, there is solution for sorting by this virtual field.

1. Entity/Some.php more about count here Extra Lazy Associations

   public function getCommentsCount()
   {
       return $this->getComments()->count();
   }
   

2. SomeAdmin.php override createQuery and configure list field

   public function createQuery($context = 'list')
   {
       $query = parent::createQuery($context);
       if ('list' === $context) {
           $rootAlias = $query->getRootAliases()[0];
           //...
           $parameters = $this->getFilterParameters();
           if ('getCommentsCount' === $parameters['_sort_by']) {
               $query
                   ->leftJoin($rootAlias.'. comments', 'cm')
                   ->groupBy($rootAlias.'.id')
                   ->orderBy('COUNT(cm.id)', $parameters['_sort_order'])
               ;
           }
           //...
       }
       return $query;
   }
   
   /**
    * @param ListMapper $listMapper
    */
   protected function configureListFields(ListMapper $listMapper)
   {
       $listMapper
           ->add('id')
           //...
           ->add(
               'getCommentsCount',
               null,
               [
                   'sortable' => true,
                   'sort_field_mapping' => ['fieldName' => 'id'],
                   'sort_parent_association_mappings' => [],
               ]
           )
        //....
    }
   

3. service.yaml add "simple" paginator (count does not work correctly)

   tags:
       - { name: sonata.admin, pager_type: "simple", ...
   

Reasons:

• subquery in orm join is not allowed
• subquery in orm orderBy is not allowed
• HIDDEN field does not work

\Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery::getFixedQueryBuilder (// for SELECT DISTINCT, ORDER BY expressions must appear in idxSelect list)

Answer 2

My answer is similar to Khalid (above) but has some key differences.

If you wrap the collection in a count( $entity->getLinks() ) then this will issue a query which returns every link association.

The downside of this is that if you have 1000s of Links associated, the memory resources required will need to be sufficient for hydrate each entity. (Which can be huge if you have thousands of different entities).

Instead, you should mark your Entities as EXTRA_LAZY and then use the --$entity->getLinks()->count()` method which will not do any hydration, instead it will only issue the COUNT queries.

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/extra-lazy-associations.html

So do the following:

   /**
     * @ManyToMany(targetEntity="Links", mappedBy="whatever", fetch="EXTRA_LAZY")
     */
    public $links;

Then you can call:

public function getTotalLinks(){
        return $this->getLinks()->count();
    }

And it will be super quick.

Answer 3

with Sonata 3.** in somwhere in Admin***.php script for listing all fields:

    protected function configureListFields(ListMapper $listMapper)
    {
        $listMapper
//...
            ->addIdentifier('getCommentsCount', IntegerType::class, [ 'label' => '#Comments'])
        ;
    } 

Where in Entity i written something like this:

    public function getCommentsCount()
    {
        return $this->comments->count();
    }

that works for me )