Database Performance issues with Sonata Admin entity listing

2
votes

I am using Symfony2 and Sonata Admin Bundle but I am encountering performance issues with entity listing.

I Have this Entity with Relations to other Entities:

class Collection
{
    /**
     * Primary Key - autoincrement value
     *
     * @var integer $id
     *
     * @ORM\Column(name="id", type="integer", nullable=false)
     * @ORM\Id
     * @ORM\GeneratedValue(strategy="IDENTITY")
     */
    private $id;

    /**
     * OWNING SIDE
     *
     * @ORM\ManyToOne(targetEntity="\App\Entity\Order")
     * @ORM\JoinColumn(name="orderId", referencedColumnName="id")
     * @var \App\Entity\Order
     */
    protected $order;

    /* ... */
}

On my AdminClass i'd like to show for each collection some order details and other related entity information.

protected function configureListFields(ListMapper $listMapper)
{
     $listMapper
         ->add('id')
         ->add(
            'order.number',
            null,
            array(
                'template' => 'App:Admin:Field/order-data.html.twig',
                'label' => 'Order Number'
            )
        );
}

But these creates fore each referenced entity one extra query. How can I resolve this?

My first Idea was to extend the createQuery Method:

public function createQuery($context = 'list')
{
    /** @var \Doctrine\ORM\QueryBuilder|QueryBuilder $query */
    $query = parent::createQuery($context);
    $query
        ->addSelect($query->getRootAliases()[0])
        ->addSelect('collectionOrder')
    ;

    $query
        ->leftJoin($query->getRootAliases()[0] . '.order', 'collectionOrder')
    ;

    return $query;
}

But this takes no effect.

So how can i manage to get all needed data with one query to reduce database load time?

1
symfonysonata-admin

1 Answers

0
votes

Add the select to the table you joined with left join.

Example:

$query
    ->leftJoin($query->getRootAliases()[0] . '.order', 'collectionOrder')
    ->addSelect('collectionOrder')
;