custom query in entity field type

Question

I build a form with entity type like this:

$form = $this->createFormBuilder()
->add('users', 'entity', array(
    'class' => 'UserBundle:Users',
        'query_builder' => function(EntityRepository $er) {
            return $er->createQueryBuilder('u')
                          ->orderBy('u.name', 'ASC');
                        },)
      )
->getForm();

Now I want to modify this form, to show only distinct users. I try this:

->add('users', 'entity', array(
                        'class' => 'UserBundle:Users',
                        'query_builder' => function(EntityRepository $er) {
                        return $er->createQuery('SELECT DISTINCT u.name FROM UserBundle:Users ORDER BY u.name ASC')->getResult();
                        },)
                      )

but Symfony throws me an exception. My question is how can I use custom query in entity field type?

---

I don't understand what you mean with last answer. My code looks like:

repository:

public function getDistinctUsers()
{
    return $this->getEntityManager()->createQuery('SELECT DISTINCT u.name FROM UserBundle:Users u ORDER BY u.name DESC')->getResult();
}

controller:

->add('users', 'entity', array(
    'class' => 'UserBundle:Users',
    'query_builder' => function(EntityRepository $er) {
            return $er->getDistinctUsers();
         },)
      )

twig:

<form action="{{ path('user') }}" method="post" {{ form_enctype(form) }}>
    {{ form_widget(form) }}
    <input type="submit" />
</form>

and it throws an exception: An exception has been thrown during the rendering of a template ("Expected argument of type "Doctrine\ORM\QueryBuilder", "array" given") ...

Becerra

"I don't understand what you mean with last answer." makes no sense at all. Please add that bit as a comment to the answer in question. You can also use the share link on the answer you are talking about, and link it to your edit. – Withershins

Answer 1

The easiest way is to add a group by in the query:

$form = $this->createFormBuilder()
  ->add('users', 'entity', array(
    'class' => 'UserBundle:Users',
    'query_builder' => function(EntityRepository $er) {
      return $er->createQueryBuilder('u')
                ->groupBy('u.id')
                ->orderBy('u.name', 'ASC');
      },)
    )
->getForm();

Sanborne

Answer 2

I don't think you can use straight DQL here; you're using the QueryBuilder object so you have to use the QueryBuilder API to build your query.

More info here:

http://www.doctrine-project.org/docs/orm/2.0/en/reference/query-builder.html

Edit

Alternatively you could group by name?

return $er->createQueryBuilder('u')
    ->groupBy('u.name')
    ->orderBy('u.name');

Edit

Okay... If you absolutely need to use DQL there is another way. You create a custom repository for your entity, and in that define a method with your custom query. Symfony's documentation covers this process here:

http://symfony.com/doc/2.0/book/doctrine.html#custom-repository-classes

Let's assume you create a method called findDistinctUsers. It's then simply a case of changing the code in your form to:

return $er->findDistinctUsers();

I haven't checked this but it should work. Hope this helps :)

Overripe

Thanks for reply. First tip works perfect, bur second one throws an exception: An exception has been thrown during the rendering of a template ("Undefined method 'findDistinctUsers'. The method name must start with either findBy or findOneBy!") – Becerra

Ah... yes. Thanks for pointing that out. I've encountered this before. I think there's some Doctrine magic in there causing that; if you change the method to getDistinctUsers() it should work fine. – Overripe

Hmmm, it doesn't work. Now I have a problem with rendering a template: An exception has been thrown during the rendering of a template ("Expected argument of type "Doctrine\ORM\QueryBuilder", "array" given"). I'm using a standard code for rendering a template http://symfony.com/doc/current/book/forms.html#rendering-the-form. There's also an another way. If I use an choice_list not entity field type then everything works fine. – Becerra

I'm guessing you're executing the query in your custom repository method. I'd say ust return a query builder object and let the form execute it instead. – Overripe

Thanks - this was the issue I struggled with. I tried just calling $er->findBy() but you're not able to. I think it should expect results rather than a querybuilder object to be more flexible. The docs should also give a bit more information, as it's hard to work out. – Gimmal

I like this approach with custom repository, but when I used in similar set up as repincln, for the source of my EntityType, it wouldn't work. If I return a query result from repository - it complains about an array, and if I just return the $query object, it complains that it expected "Doctrine\ORM\QueryBuilder", but "Doctrine\ORM\NativeQuery" was given. And I do need the NativeQuery to make my call. – Pismire

Answer 3

Don't use in Repository method:

->getQuery()
->getResult();

just alone:

->createQueryBuilder('u')
->groupBy('u.name')
->orderBy('u.name');

like in 'query_builder' => function (EntityRepository $er) {

Canticle