I’ve found many different opinions on the best way to use Doctrine query builder in Symfony 2. I needed to return a query joining four tables. I hit many errors before finding a working solution.
The requirement was to use a custom query to build a select list in a form. Symfony’s documentation recommends using the repository for this.
First thing to bear in the mind is that most repository functions return a result generated with createQuery. This time I needed a function returning a query builder object.
I also had two parameters to pass. These can be passed to the form in the $options array (remembering to add them to the form’s setDefaults). My setParameters were ignored until I applyed them to the where clauses.
The most difficult error was a ‘500 End of script output’. The query needs to return the entity object, and I has only specified the field.
The form:
public function buildForm(FormBuilderInterface $builder, array $options) { $customer = $options['customer']; $type = $options['type']; $builder ->add('description', 'entity', array( 'class' => 'MyBundle:TableA', 'query_builder' => function( EntityRepository $er ) use ( $customer, $type ) { return $er->findCatTypesQuery( $customer, $type ); }, .... public function setDefaultOptions(OptionsResolverInterface $resolver) { $resolver->setDefaults(array( 'data_class' => 'MyBundle\Entity\TableA', 'customer' => 1, 'type' => '', )); }
TableA’s repository:
$em = $this->getEntityManager(); $query = $em->createQueryBuilder( ) ->select(a) ->from( 'MyBundle:TableA', a') ->innerJoin('b.item', 'b') ->innerJoin('c.item', 'c') ->innerJoin('d.item', 'd') ->where('a.type = :type')->setParameter('type', $type) ->andWhere('b.customer = :customer')->setParameter('customer', $customer) ->orderBy('a.description') ; return $query;