Custom Query within configureShowField in Sonata Admin Class

[Prakash Thapa]

I have following code in my Admin Class:

protected function configureShowField(ShowMapper $showMapper)
{
    $showMapper
        ->with('Checkout Details')
            ->add('created')
            ->add('amount')
            ->add('gateway.name')
            ->end()
        ->with('Consumer Details')
            ->add('consumer.id', null, array('label' => 'User ID'))
            ->add('consumer.username', null, array('label' => 'User Name'))
            ->end()
        ->with('Last Checkouts')
            ->add('last_checkouts', 'entity', array(
                    'class' => 'VendorMyBundle:Checkout',
                    'query_builder' => function(EntityRepository $er) {
                        // how to query to fetch last 10 checkout requests for the current consumer.id ???
                    },
                    'template' => 'VendorMyBundle:CRUD:last_checkouts.html.twig'
            ))
            ->end()
    ;
}

and I would like to make a custom query to fetch last 10 checkout request for the same user.

could anyone let me know how can I fetch the data and display it in Twig template?

thanks 

[Jakala]

hello:

first, you need the actual consumer id, with something like this:

    $consumer_id = $this->getSubject()->getId();

now, you must send the $consumer_id to queryBuilder, with "use" sentence:

     'query_builder' => function(EntityRepository $er) use ($consumer_id) {

with this, you can obtain your query and filter:

        $qr = $er->createQueryBuilder('o')

        ->innerJoin('consumer', 'c')

        ->where('c.id = :param')

        ->setParameter('param', $consumer_id);

        return $qr;

this is an example, maybe you must to adjust as you need.

[Prakash Thapa]

first, you need the actual consumer id, with something like this:

    $consumer_id = $this->getSubject()->getId();

this will return checkout id instead of consumer id

[Jakala]

Remember: $this is an example, maybe you must to adjust as you need :)

possible:

$consumer_id = $this->getSubject()->getConsumer()->getId();


other thing:


    ->setMaxResults(10);

at the end of your querybuilder...

[Prakash Thapa]

$consumer_id = $this->getSubject()->getConsumer()->getId();

Thanks, this returns the correct Consumer ID.

By now, I have following code:

protected function configureShowField(ShowMapper $showMapper)
{

$consumer_id = $this->getSubject()->getConsumer()->getId();

$showMapper
->with('Checkout Details')
->add('created')
->add('amount')
->add('gateway.name')
->end()
->with('Consumer Details')
->add('consumer.id', null, array('label' => 'User ID'))
->add('consumer.username', null, array('label' => 'User Name'))
->end()
->with('Last Checkouts')
->add('last_checkouts', 'entity', array(
       'class' => 'VendorMyBundle:Checkout',

       'query_builder' => function (EntityRepository $er) use ($consumer_id) {
               $queryBuilder = $er->createQueryBuilder('cr');
               $queryBuilder
                       ->where('consumer = :consumer_id')
                       ->orderBy('created', 'DESC')
                       ->limit(10)
                       ->setParameter(':consumer_id', $consumer_id);
               return $queryBuilder;

       },
       'template' => 'VendorMyBundle:CRUD:last_checkouts.html.twig'))
->end();
}

and this doesn't seems to be working.

could you let me know if there is any mistakes?

Furthermore, how can I display it on 'last_checkouts.html.twig' template file?

[Jakala]

hello again:

in querybuilder you must use ->setMaxResults(10) instead of ->limit(10).

what error? can you put it here?