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!

  • 2017-05-15 Diego Aguiar

    Hey Cesar!

    Can you tell me which problems are you experiencing while following our tutorial for writing "Raw SQL Queries" ?

    Cheers!

  • 2017-05-15 Cesar Delgado

    Hello.
    Please, can you tell me how to custom a query in the EntityType using DBAL? I have tried following the explanation in https://knpuniversity.com/s... but it was not successful.
    I hope you can help me.

  • 2017-02-17 weaverryan

    Yes! Victory! It's always the simplest things ;). Enjoy!

  • 2017-02-16 Macarena Paz Diaz Colomes

    Ryan!
    My "PHP Language level is set to PHP 5.6", So, then I realized that I have a 2015 version of PHP STORM!.
    I just updated it and Voila!... Everything works perfectly now.

    Thank you so much!

  • 2017-02-16 weaverryan

    Ah, I have an idea! Try this:

    1) Go to File -> Settings in PHPStorm
    2) Open the "Languages and Frameworks" section, then click "PHP"

    What version is your "PHP Language Level" set to? Make sure it's set to PHP 5.5 or higher. The ::class syntax is new in PHP 5.5, so if you have PHP 5.4 or lower set here, PhpStorm won't auto-complete it :).

    If this is not the problem...I'm stumped, other than making sure you have the latest version of PHPStorm.

    Cheers!

  • 2017-02-15 Macarena Paz Diaz Colomes

    Hi Victor,
    I tried everything you say with no luck.
    On the video, second 0:42, you write “SubF” and have autocompletion for SubFamily.
    When I do the same, I don’t have autocompletion for that class, unless I put new before it.
    For now it’s ok, I write new, I get autocompletion and then I get rid of the new, but it wold be nice to find the problem.

    Thanks!

  • 2017-02-15 Victor Bocharsky

    Hi Macarena,

    Hm, it's weird because that is a core feature of PhpStorm. Try to reload PhpStorm, i.e. close it and open your project again. Btw, is Power save mode disable? You can find this option in "File" -> "Power save mode". If it's enabled, then PhpStorm could constrains autocomplition. Also keep in mind, that when PhpStorm do indexing your project, i.e. when you see progress bar in the right bottom corner - you don't have autocompletion too.

    Cheers!

  • 2017-02-14 Macarena Paz Diaz Colomes

    Hello!.

    I have a slight problem. I’m not getting autocompletion of any of the clases created in the AppBundle folder.
    For example, in GenusFormType.php I don't have autocompletion for SubFamily class.

    I marked the src directory as source route and excluded de cache folder, but the autocompletion still does not work.
    Hope you can help me.

    Thanks

  • 2017-02-14 Victor Bocharsky

    Wow, fast fix! Great! ;)

    Cheers!

  • 2017-02-14 Blueblazer172

    I fixed it. It was my fault i missed the ending parenthensis :P

  • 2017-02-14 Victor Bocharsky

    Hey Blueblazer172 ,

    Haha, really awful ;) Look closely to your mapping annotations in SubFamily entity. You probably missed parenthesis or something else there. If you don't see any problems - could you post your mapping here? Just SubFamily properties without any methods and I'll help you ;)

    Cheers!

  • 2017-02-14 Blueblazer172

    got an awful error on the SubFamily.php

    AnnotationException in AnnotationException.php line 42:
    [Syntax
    Error] Expected
    Doctrine\Common\Annotations\DocLexer::T_CLOSE_PARENTHESIS, got '@' at
    position 74 in class AppBundle\Entity\SubFamily.

  • 2017-02-13 weaverryan

    Hey Stan!

    Awesome question! And something we didn't talk about during the tutorial. You can pass extra options to your form, and in this case, you should pass a 'user' option. So, suppose your form class is called ProductFormType:


    class ProductFormType
    {
    public function buildView(FormView $view, FormInterface $form, array $options)
    {
    // this will be your User class, which you can use in your query_builder
    // see the below controller code to see where this comes from
    $user = $options['user'];
    }

    public function configureOptions(OptionsResolver $resolver)
    {
    $resolver->setDefaults([
    'data_class' => Product::class,
    // this is important! It makes it "legal" to pass your form a "user" option
    'user' => null,
    ]);
    }
    }

    Then, you'll pass a "user" option into your form from your controller!


    public function newAction()
    {
    $product = new Product();
    $form = $this->createForm(ProductFormType::class, $product, [
    'user' => $this->getUser()
    ]);
    }

    Does that make sense? It's not needed too often, but you can totally pass options into your form :).

    Cheers!

  • 2017-02-13 Stan

    How to make custom query (or basically populate EntityType) with related records?

    I can do something like this:

    'query_builder' => function (UserRepository $repo) {
    return $repo->getRelatedRecords();
    },

    But how do I pass the User which related records I need?

  • 2017-01-13 weaverryan

    Hey Carlos!

    Yea, it's a little weird I know. The reason is actually due to a performance optimization internally. For example, suppose you're building an API and the user will submit 10 product ids. Internally, you're processing that data through the form component, and you're using the EntityType with multiple->true for this "products" field. And, suppose you have 5k products. If you actually executed the query in order to setup the form, then you would need to load 5k products. BUT, because we're *only* passing a query builder, on submit, the form is smart: it uses the query builder to *only* look up the 10 products.

    So... performance! But it would be a little bit more natural if you actually executed the query - I agree!

    Cheers!

  • 2017-01-12 Carlos

    Hi Rayn,
    Why do not we use the getQuery() and execute() methods in the repository query, when we are a query_builder in the formType?
    Thanks

  • 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/qu.... 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 :)