CODEWITHSUNDEEP
symfonydoctrine-ormsonata-adminsymfony-sonatasonata
Aaron
Aaron

Reputation: 171

How to customize SQL query for entity associations in Sonata Admin edit view

I have an entity 'Contact' which has a OneToMany association to another entity 'Invoice':

// src/AppBundle/Entity/Contact.php
/**
 * @var Collection
 *
 * @ORM\OneToMany(targetEntity="Invoice", mappedBy="contact", cascade={"persist", "remove"}, orphanRemoval=true)
 **/
private $invoices;

// src/AppBundle/Entity/Invoice.php
/**
 * @var Contacts
 *
 * @ORM\ManyToOne(targetEntity="Contact", inversedBy="invoices")
 * @ORM\JoinColumn(name="id_contact_fk", referencedColumnName="id_contact_pk")
 **/
private $contact;

I then have a Sonata Admin class 'ContactAdmin' which displays this association in the edit view:

// src/AppBundle/Admin/ContactAdmin.php
protected function configureFormFields(FormMapper $formMapper)
{
    $formMapper
        ->tab('Invoices')
            ->with('Invoices')
                ->add('invoices', 'sonata_type_collection', array(
                    'btn_add' => false,
                    'required' => false
                ), array(
                    'edit' => 'inline',
                    'inline' => 'table'
                ))
            ->end()
        ->end();
}

That works fine except some contacts have hundreds of invoices going back for years. I need to display only invoices for the current year.

It doesn't look like there's any way to use a dynamic value (something like YEAR(CURDATE() in mysql) in place of a join column when mapping an association in Doctrine. So it seems what I need to do is somehow override the query that Sonata Admin / Doctrine uses when the ContactAdmin edit view is being rendered.

I know that the createQuery() method in a Sonata Admin class can be overridden but (correct me if I'm wrong here) this is only called for the query used to generate the list view.

There is the sonata.admin.event.configure.form event that I could act on but I'm not sure if there is any way I could modify the query from that context?

How can I go about this?

Upvotes: 1

Views: 2160

Answers (2)

amacrobert
amacrobert

Reputation: 3159

The accepted solution does allow you to limit the options to a query, but it will not populate the form field with the values after saving the entity.

Use the query_builder option instead:

$formMapper->add('invoices', null, [
    'query_builder' => function(\Doctrine\ORM\EntityRepository $invoice_repo) {
        return $invoice_repo
            ->createQueryBuilder('invoice')
            ->where('invoice.date > :date')
            ->setParameter('date', new DateTime('1 year ago'))
        ;
    }
]);

Upvotes: 0

Aaron
Aaron

Reputation: 171

After some digging I discovered that the sonata_type_collection form type accepts an undocumented parameter named 'data'. You can pass it a Collection of objects directly and it uses those.

Upvotes: 3

Related Questions