symfonydoctrine-ormsonata-adminsymfony-sonatasonata

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?


Solution

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