Allow sorting and filtering for virtual fields

[waiting-for-dev]

For listMapper it is possible to add a virtual field representing an entity method. For example:

$listMapper->add('getTotalNumberOfComments', 'integer');

Where getTotalNumberOfComments could be a simply method in the entity:

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

But this virtual field can't be used in datagridMapper nor in the sort definition. It forces you to introduce in your database a redundant field which can be automatically calculated with a hook before persisting. Not the ideal, indeed.

Thanks a lot for your great work!

[michelsalib]

What if directly do:

$listMapper->add('TotalNumberOfComments', 'doctrine_orm_callback', array(
    'callback' => function($queryBuilder, $alias, $field, $value) {
        if (!$value || !isset($value['value']) || !isset($value['value']['type']) || !$value['value']['type']) {
            return;
        }

        $operators = array(
            NumberType::TYPE_EQUAL            => '=',
            NumberType::TYPE_GREATER_EQUAL    => '>=',
            NumberType::TYPE_GREATER_THAN     => '>',
            NumberType::TYPE_LESS_EQUAL       => '<=',
            NumberType::TYPE_LESS_THAN        => '<',
        );

        $operator = $operators[$value['value']['type']];

        $queryBuilder->andWhere('SIZE('$alias.'.comments) '.$operator.' :size');
        $queryBuilder->setParameter('size', $value['value']['value']);
    },
    'field_type' => 'sonata_type_filter_number',
))

Whereas the whole operator part should be optimized by the bundle, this case is marginal enough to deserve no more than a documentation entry. Moreover, your desired way of solving the issue introduces a serious performance issue, which is not the case using my proposal.

[bunsky]

I have the same problem but the code michelsalib causes this error:

Catchable Fatal Error: Argument 1 passed to Sonata\DoctrineORMAdminBundle\Datagri\ProxyQuery::entityJoin() must be an array, null given, called

Another solution is there?

thank you

[balazscsaba2006]

Has anyone found a usable solution for this? Without creating prePersist() hooks?

Thank you.

[webdevilopers]

:+1:

[j1v3]

I have the same error message that @bunsky

PHP Catchable fatal error:  Argument 1 passed to Sonata\DoctrineORMAdminBundle\Datagrid\ProxyQuery::entityJoin() must be of the type array, null given

Someone has the solution?

Thanks

[florentdestremau]

Still waiting for an anwser for such a basic feature

[greg0ire]

It's not a basic feature, because you cannot sort by a virtual field without introducing a major performance issue : if you do it, you are supposed to compute the virtual field value for every row in the table before being able to sort.

Also, I would recommend changing your tone, we don't owe you anything, do we?

[greg0ire]

without introducing a major performance issue

unless you use a function-based index, but this is not supported by doctrine yet , it might be supported in doctrine 3.x though

[florentdestremau]

hi,

I apologize for being rude, I spent nearly 2 hours trying to get something to work, and ended up here a bit angry. I shouldn't have posted this comment.

As it is for statistics purpose I do not mind having a performance issue for now, and i wanted to avoid creating an index just for this. I guess I'll have to think about it again :sweat_smile:

[greg0ire]

No worries, you're a great person for admitting this. You may post this as a question on stack overflow, and I will put a bounty on it as soon as allowed :)

[TheRatG]

I found a solution.

1. Entity/Some.php more about count here http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/tutorials/extra-lazy-associations.html

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

2. SomeAdmin.php

   public function createQuery($context = 'list')
   {
       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'])
               ;
           }
           //...
   }
   
   /**
    * @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

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

More than 2 hours 😄 Because:

• 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)

[Fedchishina]

@TheRatG Thank you very much!!! It is working!!!

[osavchenko]

@TheRatG Thank you so much! It's amazing. Maybe it should be somehow documented in sonata recipes, @greg0ire or it's useless?

[greg0ire]

Please do submit a cookbook article :)

[osavchenko]

@TheRatG I have tried your solution today and it doesn't work because of group by statement - it can't calculate total number of records. Do you have workaroud for this?

[core23]

This issue is very old. Feel free to reopen, if the error still exists.

A PR would be welcome if you reopen this issue.

[Oleg-Pro]

I try your solution but I get error: [Syntax Error] line 0, col 184: Error: Expected known function, got 'COUNT'

Could you please tell me what's wrong with it?

Here's what I do:

    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);

        if ('list' === $context) {
            $parameters = $this->getFilterParameters();

            if ('getVotesCount' === $parameters['_sort_by']) {
                $rootAlias = $query->getRootAliases()[0];
                $query
                    ->leftJoin($rootAlias.'. votes', 'v')
                    ->groupBy($rootAlias.'.id')
                    ->orderBy('COUNT(v.id)', $parameters['_sort_order']);
            }
        }
      return $query;
    }

[osavchenko]

Please, try such query:

                $query
                    ->addSelect('COUNT(v.id) as v_id_count')
                    ->leftJoin($rootAlias.'. votes', 'v')
                    ->groupBy($rootAlias.'.id')
                    ->orderBy('v_id_count', $parameters['_sort_order']);

[Oleg-Pro]

Thank you for your response: I try this one:

    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);

        if ('list' === $context) {
            $parameters = $this->getFilterParameters();

            if ('getVotesCount' === $parameters['_sort_by']) {
                $rootAlias = $query->getRootAliases()[0];
                $query
                    ->addSelect('COUNT(v.id)', 'v_id_count')
                    ->leftJoin($rootAlias.'. votes', 'v')
                    ->groupBy($rootAlias.'.id')
                    ->orderBy('v_id_count', $parameters['_sort_order']);
            }
        }

        return $query;
    }

And I get error [Semantical Error] line 0, col 184 near 'v_id_count ASC,': Error: 'v_id_count' is not defined.

[osavchenko]

Updated my original response. Was a typo

[Oleg-Pro]

I change to this one:

    public function createQuery($context = 'list')
    {
        $query = parent::createQuery($context);

        if ('list' === $context) {
            $parameters = $this->getFilterParameters();

            if ('getVotesCount' === $parameters['_sort_by']) {
                $rootAlias = $query->getRootAliases()[0];

                $query
                    ->addSelect('COUNT(v.id) as v_id_count')
                    ->leftJoin($rootAlias.'. votes', 'v')
                    ->groupBy($rootAlias.'.id')
                    ->orderBy('v_id_count', $parameters['_sort_order']);
            }
        }

        return $query;
    }

But the error is still here:

[Semantical Error] line 0, col 184 near 'v_id_count ASC,': Error: 'v_id_count' is not defined.

[Oleg-Pro]

I display sql query:

` public function createQuery($context = 'list') { $query = parent::createQuery($context);

    if ('list' === $context) {
        $parameters = $this->getFilterParameters();

        if ('getVotesCount' === $parameters['_sort_by']) {
            $rootAlias = $query->getRootAliases()[0];

            $query
                ->addSelect('COUNT(v.id) as v_id_count')
                ->leftJoin($rootAlias.'. votes', 'v')
                ->groupBy($rootAlias.'.id')
                ->orderBy('v_id_count', $parameters['_sort_order']);

            var_dump($query->getQuery()->getSQL());
            exit;
        }
    }

    return $query;
}`

I get this query:

SELECT n0_.id AS id_0, n0_.nomination AS nomination_1, n0_.player_id AS player_id_2, n0_.sort_order AS sort_order_3, n0_.in_main_list AS in_main_list_4, n0_.active AS active_5, n0_.created_at AS created_at_6, n0_.updated_at AS updated_at_7, COUNT(v1_.id) AS sclr_8, n0_.poll_id AS poll_id_9 FROM nominees n0_ LEFT JOIN votes v1_ ON n0_.id = v1_.nominee_id GROUP BY n0_.id ORDER BY sclr_8 DESC;

[Oleg-Pro]

Field is added in configureListFields like this one:

        $listMapper
            ->add(
                'getVotesCount',
                null,
                [
                    'sortable' => true,
                    'sort_field_mapping' => ['fieldName' => 'id'],
                    'sort_parent_association_mappings' => [],
                ]
            );

[hainovsky]

This way it would firstly sort by ID field and only then by COUNT that will give you incorrect sort result on the list. One option is to introduce dummy property for the Entity (with value "1" for all records) and use it instead of id in 'sort_field_mapping' => ['fieldName' => 'id'], but that could be a little bit messy and the other option still figuring out..

P.S. The problem is that ProxyQuery internally adds sort_field_mapping as first one and only after any yours amendments in configureQuery method