Can i use query in form builder to get filtered collection in symfony form

Question

IN the AcmePizza BUndle this is working fine

->add('pizza', 'entity', array(
                'class'         => 'Acme\PizzaBundle\Entity\Pizza',
                'query_builder' => function ($repository) { return $repository->createQueryBuilder('p')->orderBy('p.name', 'ASC'); },
            ))

Can i do something like that in collection

->add('userTasks','collection',array('type' => new UserTaskType(),
                    'class'         => 'acme\myBundle\Entity\UserTask',
                    'query_builder' => function ($repository) { return $repository->createQueryBuilder('p')->orderBy('p.name', 'ASC'); },
                ))

Answer 1

Assuming Your userTasks is an relationship You will find answer for Your case here. These is just how to sort but if You had required some WHERE conditions it is not so simple but neither it is hard.

I had to filter out some entities, the key to solve it was to create set/get method in entity class returning required set.

In my case it looks like this

/**
 * Set values
 *
 * @param ArrayCollection $values
 * @return Attribute
 */
public function setCustomValues($values)
{
    $result = $this->getNotCustomValues();
    foreach ($values as $value)
    {
        $value->setAttribute($this);
        $result->add($value);
    }
    $this->values = $result;

    return $this;
}

/**
 * Get values
 *
 * @return \Doctrine\Common\Collections\Collection
 */
public function getCustomValues()
{
    $result = new ArrayCollection();
    foreach ($this->values as $value) {
        if($value->getCustom()) {
            $result->add($value);
        }
    }
    return $result;
}

And when creating form, name for a field is "customvalues" instead of "values" So my collection contains only values with custom field true.

Answer 2

You often want to filter collection when you are updating an entity, not having a new one, right?

Here is a working solution, this is an example from controller (CRUD):

public function updateAction($id)
{
    $service = $this->getServiceRepository()->loadOne('id', $id);
    $this->checkPermission($service);

    $this->filterInventoryByPrimaryLocation($service);

    if($this->getFormHandler()->process('service_repair_service', $service, array('type' => 'update')))
    {
        $this->getEntityManager()->process('persist', $service);

        return $this->render('ServiceRepairBundle:Service:form_message.html.twig', [
            'message' => $this->trans('Service has been updated successfully.')
        ]);
    }

    return $this->render('ServiceRepairBundle:Service:form.html.twig', [
        'form' => $this->getFormHandler()->getForm()->createView(),
    ]);
}

private function filterInventoryByPrimaryLocation(Service $service)
{
    $inventory = $service->getInventory();

    $criteria = Criteria::create()
        ->where(Criteria::expr()->eq('location', $this->getUser()->getPrimaryLocation()))
        ->orderBy(array('timeInsert' => Criteria::ASC));

    $service->setInventory($inventory->matching($criteria));
}

$service = ENTITY, $inventory = ArrayCollection ( $service->getInventory() )

The key here is to use Doctrine's Criteria, more info here:

http://doctrine-orm.readthedocs.org/en/latest/reference/working-with-associations.html#filtering-collections

Also think of moving the Criteria in entity itself, make a public method there. When you load it from database, you can fire that method using doctrine's postLoad lifecycle callback. Ofcourse putting it in entity will work, if you don't require any services or things like that.

Another solution would probably be, to move the Criteria in a Form Event inside Form class, if you need filtering only inside a form.

If you need the collection filtering to be done transparently across whole project, write a doctrine listener and put the code inside a postLoad() method. You can also inject dependencies in doctrine listener, but I recommend injecting container itself, because of lazy loading other services, so you do not get circular service references.

Good luck!