Sonataadminbundle: 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();
    }

1. 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' => [],
                ]
            )
         //....
     }

1. 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 SonataDoctrineORMAdminBundleDatagridProxyQuery::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' => [],
                ]
            );