Buy

Custom Query in EntityType

As cool as it is that it's guessing my field type, I am actually going to add EntityType::class to use this type explicitly:

43 lines src/AppBundle/Form/GenusFormType.php
... lines 1 - 4
use Symfony\Bridge\Doctrine\Form\Type\EntityType;
... lines 6 - 10
class GenusFormType extends AbstractType
{
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
... line 16
->add('subFamily', EntityType::class, [
... lines 18 - 22
])
... lines 24 - 32
;
}
... lines 35 - 41
}

I don't have to do this: I just want to show you guys what a traditional setup looks like.

Now, do nothing else and refresh. It'll still work, right? Right? Nope!

The required class option is missing! As I just finished saying, we must pass a class option to the EntityType. We got away with this before, because when it's null, Symfony guesses the form "type" and the class option.

Set the option to SubFamily::class - and alternate syntax to the normal AppBundle:SubFamily:

45 lines src/AppBundle/Form/GenusFormType.php
... lines 1 - 4
use AppBundle\Entity\SubFamily;
... lines 6 - 12
class GenusFormType extends AbstractType
{
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
... line 18
->add('subFamily', EntityType::class, [
... line 20
'class' => SubFamily::class,
... lines 22 - 24
])
... lines 26 - 34
;
}
... lines 37 - 43
}

The query_builder Option

Now that our form is put back together, I have a second challenge: make the select order alphabetically. In other words, I want to customize the query that's made for the SubFamily's and add an ORDER BY.

Head back to the EntityType docs. One option jumps out at me: query_builder. Click to check it out. OK, it says:

If specified, this is used to query the subset of options that should be used for the field.

And actually, I need to search for query_builder: I know there's a better example on this page. Here it is!

So, if you pass a query_builder option and set it to an anonymous function, Doctrine will pass that the entity repository for this specific entity. All we need to do is create whatever query builder we want and return it.

In the form, add query_builder and enjoy that auto-completion. Set this to a function with a $repo argument:

45 lines src/AppBundle/Form/GenusFormType.php
... lines 1 - 12
class GenusFormType extends AbstractType
{
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
... line 18
->add('subFamily', EntityType::class, [
... lines 20 - 21
'query_builder' => function(SubFamilyRepository $repo) {
... line 23
}
])
... lines 26 - 34
;
}
... lines 37 - 43
}

Now, I like to keep all of my queries inside of repository classes because I don't want queries laying around in random places, like in a form class. But, if you look, I don't have a SubFamilyRepository yet.

No worries - copy the GenusRepository, paste it as SubFamilyRepository. Rename that class and clear it out:

16 lines src/AppBundle/Repository/SubFamilyRepository.php
... lines 1 - 2
namespace AppBundle\Repository;
... lines 4 - 5
use Doctrine\ORM\EntityRepository;
class SubFamilyRepository extends EntityRepository
{
... lines 10 - 14
}

Open the SubFamily entity and hook it up with @ORM\Entity(repositoryClass="") and fill in SubFamilyRepository:

45 lines src/AppBundle/Entity/SubFamily.php
... lines 1 - 6
/**
* @ORM\Entity(repositoryClass="AppBundle\Repository\SubFamilyRepository")
... line 9
*/
class SubFamily
... lines 12 - 45

Great! Back in our form, we know this repo will be an instance of SubFamilyRepository:

45 lines src/AppBundle/Form/GenusFormType.php
... lines 1 - 5
use AppBundle\Repository\SubFamilyRepository;
... lines 7 - 12
class GenusFormType extends AbstractType
{
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
... line 18
->add('subFamily', EntityType::class, [
... lines 20 - 21
'query_builder' => function(SubFamilyRepository $repo) {
... line 23
}
])
... lines 26 - 34
;
}
... lines 37 - 43
}

Return $repo-> and a new method that we're about to create called createAlphabeticalQueryBuilder():

45 lines src/AppBundle/Form/GenusFormType.php
... lines 1 - 12
class GenusFormType extends AbstractType
{
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
... line 18
->add('subFamily', EntityType::class, [
... lines 20 - 21
'query_builder' => function(SubFamilyRepository $repo) {
return $repo->createAlphabeticalQueryBuilder();
}
])
... lines 26 - 34
;
}
... lines 37 - 43
}

Copy that name and head into the repository to create that function. Inside, return $this->createQueryBuilder('sub_family') and then order by sub_family.name, ASC:

16 lines src/AppBundle/Repository/SubFamilyRepository.php
... lines 1 - 7
class SubFamilyRepository extends EntityRepository
{
public function createAlphabeticalQueryBuilder()
{
return $this->createQueryBuilder('sub_family')
->orderBy('sub_family.name', 'ASC');
}
}

Done! The query_builder method points here, and we handle the query.

Alright, try it out! Nailed it! As far as form options go, we probably just conquered one of the most complex.

Leave a comment!

  • 2016-11-10 weaverryan

    Hey diarselimi92!

    Do you want to do this for a performance improvement? By default, the EntityType queries for *full* objects, so it would query for all of the fields. This is done so that when the selected option is set back on your entity, that is a *full* entity object. You could, in theory, use a partial objects (http://stackoverflow.com/quest.... Or, you could use the normal ChoiceType, and build your own "data transformer" that would do the custom query itself, and then transform the submitted id back to the full object.

    Let me know what you think - happy to offer more guidance.

    Cheers!

  • 2016-11-10 diarselimi92

    Hi, what if i wan't to limit the number of columns i wan't to query, ex: a.id, a.name ?
    Can that be done in the query builder in formtype ?

  • 2016-11-01 weaverryan

    Sweet! Great work! It will feel like setting up the relationship is a bit more work at first, but it will pay off BIG... like in this case, when the EntityType just suddenly works as soon as that's a relation :).

    Cheers!

  • 2016-10-31 Roel Beckers

    Yo Ryan!

    Thanks for your answer. In the table, the field supervisorId is an integer and I'm trying to store just the number id from the Supervisor. But storing the object, as you suggest makes indeed much more sense.

    Was able to figure it out now and it works. Thanks for your insights!

    Thanks!
    Roel

  • 2016-10-31 weaverryan

    Yo Roel!

    Hmm. So let me ask you a bit more about your setup. I noticed that your field is called supervisorId. Is this really an integer field? Or is it a relationship (e.g. ManyToOne) field? What the EntityType does is work when the field that you've assigned it to is an entity *object*, it won't work if you're trying to use a true integer field (i.e. if supervisorId is an integer field that literally stored things like "5").

    Am I right about this field? If so, what's your reason for doing it this way? It's totally legal, but will make your life a bit harder :). If it *is* the case, you might be better suited with a ChoiceType, where you simply query for all of your supervisor Users and create a simple array with the id as the value and their fullname as the label for the options.

    Cheers!

  • 2016-10-31 Roel Beckers

    Hi there guru's,

    In your example, the label of the dropdown and the value stored in the database is the same.
    What to do when I want to show the text as label on the dropdown (retrieved from database) and store the linked id from these values?
    Currently always the label is passed and I get the error 'Incorrect integer value'.

    Here is a part of the code with the EntityType from my UserCreateForm.php:

    ->add('supervisorId', EntityType::class, [
    'class' => User::class,
    'choice_label' => 'fullname',
    'choice_name' => 'id',
    'placeholder' => 'Choose a Supervisor',
    'query_builder' => function(UserRepository $repo){
    return $repo->findAllSupervisorsOrderedByFirstname();
    }

    This is my UserRepository.php:

    createQueryBuilder('user')
    ->andWhere('user.access_role = :accessrole')
    ->setParameter('accessrole', "ROLE_SUPERVISOR")
    ->orderBy('user.firstname', 'ASC');
    }
    }
    ])

    Thanks already!

    Roel

  • 2016-09-06 Marco La Cugurra

    Thanks Victor, yes now it's working :)

  • 2016-09-05 Victor Bocharsky

    Hey Marco,

    Looks like you forgot to use a namespace for `BookingRepository` class in `BookingType`, that's why it's trying to load `BookingRepository` with `AppBundle\Form` namespace. Add the use AppBundle\Repository\BookingRepository; above the `BookingType` class, it should help.

    Cheers!

  • 2016-09-04 Marco La Cugurra

    Hello :) I keep on having this error while implementing a custom query on my BookingType.php

    "Catchable Fatal Error: Argument 1 passed to AppBundle\Form\BookingType::AppBundle\Form\{closure}() must be an instance of AppBundle\Form\BookingRepository, instance of AppBundle\Repository\BookingRepository given"

    here is my Booking class

    id;
    }

    /**
    * Set bookingDate
    *
    * @param \DateTime $bookingDate
    *
    * @return Booking
    */
    public function setBookingDate($bookingDate)
    {
    $this->bookingDate = $bookingDate;

    return $this;
    }

    /**
    * Get bookingDate
    *
    * @return \DateTime
    */
    public function getBookingDate()
    {
    return $this->bookingDate;
    }

    /**
    * Set bookingTime
    *
    * @param \DateTime $bookingTime
    *
    * @return Booking
    */
    public function setBookingTime($bookingTime)
    {
    $this->bookingTime = $bookingTime;

    return $this;
    }

    /**
    * Get bookingTime
    *
    * @return \DateTime
    */
    public function getBookingTime()
    {
    return $this->bookingTime;
    }

    /**
    * Set bookingComments
    *
    * @param string $bookingComments
    *
    * @return Booking
    */
    public function setBookingComments($bookingComments)
    {
    $this->bookingComments = $bookingComments;

    return $this;
    }

    /**
    * Get bookingComments
    *
    * @return string
    */
    public function getBookingComments()
    {
    return $this->bookingComments;
    }

    /**
    * Set status
    *
    * @param \AppBundle\Entity\Status $status
    *
    * @return Booking
    */
    public function setStatus(\AppBundle\Entity\Status $status = null)
    {
    $this->status = $status;

    return $this;
    }

    /**
    * Get status
    *
    * @return \AppBundle\Entity\Status
    */
    public function getStatus()
    {
    return $this->status;
    }

    /**
    * Set users
    *
    * @param \AppBundle\Entity\User $users
    *
    * @return Booking
    */
    public function setUsers(\AppBundle\Entity\User $users = null)
    {
    $this->users = $users;

    return $this;
    }

    /**
    * Get users
    *
    * @return \AppBundle\Entity\User
    */
    public function getUsers()
    {
    return $this->users;
    }

    /**
    * Set dogs
    *
    * @param \AppBundle\Entity\Dog $dogs
    *
    * @return Booking
    */
    public function setDogs(\AppBundle\Entity\Dog $dogs = null)
    {
    $this->dogs = $dogs;

    return $this;
    }

    public function __toString()
    {
    return $this->dogs;
    }

    /**
    * Get dogs
    *
    * @return \AppBundle\Entity\Dog
    */
    public function getDogs()
    {
    return $this->dogs;
    }
    /**
    * Constructor
    */
    public function __construct()
    {
    $this->services = new \Doctrine\Common\Collections\ArrayCollection();
    }

    /**
    * Add service
    *
    * @param \AppBundle\Entity\Service $service
    *
    * @return Booking
    */
    public function addService(\AppBundle\Entity\Service $service)
    {
    $this->services[] = $service;

    return $this;
    }

    /**
    * Remove service
    *
    * @param \AppBundle\Entity\Service $service
    */
    public function removeService(\AppBundle\Entity\Service $service)
    {
    $this->services->removeElement($service);
    }

    /**
    * Get services
    *
    * @return \Doctrine\Common\Collections\Collection
    */
    public function getServices()
    {
    return $this->services;
    }
    }

    BookingType:

    add('dogs', EntityType::class, [
    'class'=>Booking::class,
    'placeholder'=>'Select a Dog',
    'query_builder' => function (BookingRepository $repo) {
    return $er->dogByUserQuery();
    }

    ])
    ->add('services')
    ->add('bookingDate', HiddenType::Class , array('data' => 'ciao' ))
    ->add('bookingTime', DateTimeType::class , array(
    'placeholder' => array(
    'year' => 'Year', 'month' => 'Month', 'day' => 'Day',
    'hour' => 'Hour', 'minute' => 'Minute'
    )))

    ;
    }

    /**
    * @param OptionsResolver $resolver
    */
    public function configureOptions(OptionsResolver $resolver)
    {
    $resolver->setDefaults(array(
    'data_class' => 'AppBundle\Entity\Booking'
    ));
    }
    }

    and my BookingRepository.php

    createQueryBuilder('booking')
    ->orderBy('booking.dogs', 'ASC');
    }
    }

    please help me :)