EntityType: Custom Query

Keep on Learning!

If you liked what you've learned so far, dive in!
Subscribe to get access to this tutorial plus
video, code and script downloads.

Start your All-Access Pass
Buy just this tutorial for $12.00

With a Subscription, click any sentence in the script to jump to that part of the video!

Login Subscribe

Right click and "Inspect Element". Look at the value of each option: it's the id of that user in the database. So, when we choose an author, this is the value that will be submitted to the server: this number. Just remember that.

Time to author another award-winning article:

Pluto: I didn't want to be a Planet Anyways

Set the publish date to today at any time, select an author and... create! Yes! The author is spacebar3@example.com and it is published.

This is way more amazing than it might look at first! Sure, the EntityType is cool because it makes it easy to create a drop-down that's populated from the database. Blah, blah, blah. That's fine. But the truly amazing part of EntityType is its data transformer. It's the fact that, when we submit a number to the server - like 17 - it queries the database and transforms that into a User object. That's important because the form system will eventually call setAuthor(). And this method requires a User object as an argument - not the number 17. The data transformer is the magic that makes that happen.

Creating a Custom Query

We can use this new knowledge to our advantage! Go back to the create form. What if we don't want to show all of the users in this drop-down? Or, what if we want to control their order. How can we do that?

Normally, when you use the EntityType, you don't need to pass the choices option. Remember, if you look at ChoiceType, the choices option is how you specify which, ah, choices you want to show in the drop-down. But EntityType queries for the choices and basically sets this option for us.

To control that query, there's an option called query_builder. Or, you can do what I do: be less fancy and simply override the choices option entirely. Yep, you basically say:

Hey EntityType! Thanks... but I can handle querying for the choices myself. But, have a super day.

Injecting Dependencies

To do this, we need to execute a query from inside of our form class. And to do that, we need the UserRepository. But... great news! Form types are services! So we can use our favorite pattern: dependency injection.

Create an __construct() method with an UserRepository argument. I'll hit alt+enter, and select "Initialize Fields" to create that property and set it. Down below, pass choices set to $this->userRepository and I'll call a new method ->findAllEmailAlphabetical().

... lines 1 - 14
class ArticleFormType extends AbstractType
{
private $userRepository;
public function __construct(UserRepository $userRepository)
{
$this->userRepository = $userRepository;
}
... line 23
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder
... lines 27 - 33
->add('author', EntityType::class, [
... lines 35 - 39
'choices' => $this->userRepository->findAllEmailAlphabetical(),
])
... line 42
}
... lines 44 - 50
}

Copy that name, go to src/Repository/, open UserRepository, and create that method. Use the query builder: return $this->createQueryBuilder('u') and then ->orderBy('u.email', 'ASC'). Finish with ->getQuery() and ->execute().

Above the method, we know that this will return an array of User objects. So, let's advertise that!

... lines 1 - 14
class UserRepository extends ServiceEntityRepository
{
... lines 17 - 21
/**
* @return User[]
*/
public function findAllEmailAlphabetical()
{
return $this->createQueryBuilder('u')
->orderBy('u.email', 'ASC')
->getQuery()
->execute()
;
}
... lines 33 - 61
}

I love it! This makes our ArticleFormType class happy. I think we should try it! Refresh! Cool! The admin users are first, then the others.

So... is EntityType Still Needed?

But... wait. Now that we're manually setting the choices option... do we even need to use EntityType anymore? Couldn't we switch to ChoiceType instead?

Actually... no! There is one super critical thing that EntityType is still giving us: data transformation. When we submit the form, we still need the submitted id to be transformed back into the correct User object. So, even though we're querying for the options manually, it is still doing this very important job for us. Remember: the true power of a field type is this data transformation ability.

Next: let's add some form validation! It might work a little differently than you expect.

Leave a comment!

  • 2020-05-14 Diego Aguiar

    Cool! First of all, welcome to Symfony! I bet you gonna love it :)

    I think you can simplify your process. What if the input field is always empty and you will only use it when a user submits a value? If he didn't, in other words, the input field is empty, you will what you said. Fetch the last record and add +1 to it, then create the new record using such value
    You can add a help message under the input field explaining what will happen if it remains empty.

    Does it sounds good?

    Cheers!

  • 2020-05-13 Brandon Peterson

    Diego, thank you so much for getting back to me. I'm using EntityType so I can make a custom query to get the last number used in the "toolspenum" column. That column is different than the id. To add some context I've created a web site for my business using procedural PHP, at the top of the page that has the form I run a mysqli query, get the last row in the database, take the value of column "toolspenum" and add 1 to it, then I use that variable as the value of the input field in my form so who ever is filling out the form doesn't have to look for the next number, but it is a text input field so if they don't want to use the next number then can erase it and use whatever is needed. I'm new to Symfony so I have been watching as many videos as I can, but I definitely don't know all the ins and outs on how to achieve what I know how to do procedure PHP style in Symfony. Again, thank you.

  • 2020-05-12 Diego Aguiar

    Hey Brandon Peterson

    I think I need a bit more of context to understand your real problem. If you are asking for a string why are you rendering as an EntityType? Is it the id value of an entity?

    Cheers!

  • 2020-05-12 Brandon Peterson

    Hello, I'm building a form that has a text field that a user supplies a number to a specific tool eg 0001. I've setup a custom query to display the last number used, eg 0004, but I want to increment that by 1, that way they don't have to look what the next number is, it would suggest it. I'm struggling on how to achieve that. I've used EntityType, custom query, but it a a drop down obviously, and I'm not sure how to increment it by one. Any suggestion would great, thank you.

  • 2020-03-05 Rob

    Diego, thank you so much for your help on this topic! I am a newbie to coding and even symfony and this type of help really helps the lights click on!

  • 2020-03-04 Diego Aguiar

    Excellent, your form is now more secure!

  • 2020-03-04 Rob

    That wouldn't be good! I looked at this and realized when I manually set the User object to my Item object in my controller, I can remove the ->add('seller') from my ItemFormType and also remove the form field {{ form_row(itemForm.seller) }} . The new and edit functions are working and when I inspect form, I can't see any reference to the user id at all.

  • 2020-03-03 Diego Aguiar

    Just be aware that someone could hack your form and change the user id of the hidden field. That's why it's better to not render a form field if you are not going to use it :)

  • 2020-03-03 Rob

    Cool I will give those a try. I also just tried the below in my form and it hides the seller field on new and edit and also persists

    ->add('seller', HiddenType::class, [
    'data' => User::class,
    'mapped' => false,
    ])

  • 2020-03-03 Diego Aguiar

    Yes, there are a couple of ways to get it removed from your Form. First, you can create a second Form, the NewArticleForm, which won't add the seller field
    Second: you can use form events in order to add dynamically the field
    Third: Pass an option to the form that indicates if the seller field should be added

    You can find more info about form events here: https://symfony.com/doc/cur...

    BTW, I would just create a new form and keep going

  • 2020-03-03 Rob

    I have the new action manually setting the logged in user to my Item "Article" object, but is it possible to filter the {{ form_row(itemForm.seller) }} field so it doesn't appear on the new.html.twig page? In my case the seller is the User object and the current logged in user is getting persisted, I'm down to trying to find best way to keep {{ form_row(itemForm.seller) }} from printing.

  • 2020-03-02 Diego Aguiar

    > I’m trying to only show the logged in user in the drop down. And the best setup would be, we are not even presented with a dropdown, because on the new action, I only want to set the current User object onto the new Article object.

    In that case, in the new action, do not print the user drop down, and manually set the logged in user into the Article object

  • 2020-02-29 Rob

    Hey Diego, kind of makes sense.

    My app is set up just like the tutorial, where if an anonymous user goes too: /admin/article/new, they will be redirected to the login page. So I’m not sure my new function needs to check if the user object is null. More I look at this I don’t think I need to use Security to get the user. At the beginning of my Admin Controller new function, $user = $this->getUser(); returns the currently logged in user.

    In the Script of Chapter 08, Ryan mentions “What if we don't want to show all of the users in this drop-down?”

    That is exactly what I am trying to do. I’m trying to only show the logged in user in the drop down. And the best setup would be, we are not even presented with a dropdown, because on the new action, I only want to set the current User object onto the new Article object.

  • 2020-02-28 Diego Aguiar

    Hey Rob

    When you call Security::getUser() it will try to get the logged in user but it may return null as well in case nobody is logged in. What you need to do is to get the logged in user in your controller's action (by using the shortcut method $this->getUser();), if you get null, then there is no logged user and you should follow the logic for when that happens, but if there is a user, then you can just pass it in to any service or repository

    Does it makes sense to you?
    Cheers!

  • 2020-02-27 Rob

    Writing some code close to the tutorial, how would we use 'choices' and the UserRepository to only show the current logged in user in the dropdown? In my app, the logged in user is the "Author" so I would only like to persist the current user. It doesn't look like Security will do it. The ->setParameter('user', $security->getUser() does autocomplete so it makes be believe Security is giving me the User object, but I get a Too few arguments on the method. I'm a newbie!! This is what Ive got:

    In buildForm:

    ->add('seller', EntityType::class, [
    'class' => User::class,
    'choice_label' => function(User $user) {
    return sprintf('(%d) %s', $user->getId(), $user->getEmail());
    },
    'placeholder' => 'Choose an seller',
    'choices' => $this->userRepository->findCurrentUser(),
    ])

    UserRepository:

    /**
    *@return User[]
    */
    public function findCurrentUser(Security $security)
    {
    return $this->createQueryBuilder('u')
    ->andWhere('u.email = :user')
    ->setParameter('user', $security->getUser())
    ->getQuery()
    ->execute()
    ;

    }

    I get a Too few arguments to the findCurrentUser method, got 0, expected 1.

  • 2019-12-09 Virgile Sahaguian

    Hello Vladimir,

    Thanks you for your fast reply,
    unfortunately that does not fix anything. :'(

    Cheers !

  • 2019-12-09 Vladimir Sadicov

    Hey Virgile Sahaguian

    Interesting! It sounds like one of Doctrine bugs 🐛 (https://github.com/doctrine... If it make sense, than I'll advice to modify your query somehow, probably add r.id will fix it, or not anyways try, and come back with some results)

    Cheers!

  • 2019-12-09 Virgile Sahaguian

    Hello,

    I got one error when i try to use a select clause into my custom query,

    /**

    * @return Object[]

    */

    public function distinctPostalCode()

    {

    return $this->getOrCreateQueryBuilder()

    ->select('r.postalCode')

    ->distinct()

    ->getQuery()

    ->execute();

    }

    Exception : Warning: spl_object_hash() expects parameter 1 to be object, string given

  • 2019-12-02 Vladimir Sadicov

    Hey Maik Tizziani

    Wooohoo! That's great! BTW typos are the most frequent issues in code =)

    Cheers!! Have a great time with courses!

  • 2019-11-30 Maik Tizziani

    found the error by myself, i had a typo in ArticleFormType.php in the configureOptions (big fingers make mistakes)

  • 2019-11-30 Maik Tizziani

    i have annotated $data as Article object but get back a array, what what went's wrong?


    /** @var Article $data */
    $data = $form->getData();
    // returns Array

    looks like the automatic casting does not work for me longer, but why? All things exactly like in the tutorial and worked until yesterday.

    As a workaround i wrote a simple parser in Article.php if someone has the same problem:


    public static function fromArray($array): Article{
    $article = new Article();
    foreach($array as $key => $field){
    $keyUp = ucfirst($key);
    if(method_exists($article,'set'.$keyUp)){
    call_user_func(array($article,'set'.$keyUp),$field);
    }
    }
    return $article;
    }

    now if you call $article = Article::fromArray($form->getData()); you get an Instance of Article.

  • 2019-10-19 Dung Le

    For those who read this thread, here is more https://symfonycasts.com/sc...

  • 2019-10-17 Victor Bocharsky

    Hey Dung,

    Glad it helped you! And thank you for the kind words about SymfonyCasts :)

    Cheers!

  • 2019-10-16 Dung Le

    Victor Bocharsky Hi, this is great, I can now go back to my project learning from your source and applying / coding to my project. Thank you so much, btw Symfonycasts is awesome resource!

  • 2019-10-16 Victor Bocharsky

    Hey Dung,

    Ah, ok, I think I got it. Yes, we do have tutorials about Symfony Forms: https://symfonycasts.com/sc... where we create a custom form type for Article entity called ArticleFormType and make it more complex in every new chapter. And about Doctrine Relations: https://symfonycasts.com/sc... where In particular to your question, you may want to take a look at:

    - https://symfonycasts.com/sc... about inner joins;
    - https://symfonycasts.com/sc... about left joins

    So, those are good courses to watch in full. But for more specific questions I'd recommend you to use our cool search on SymfonyCasts that searches in chapter titles, scripts and even code that we're showing in our screencasts or comments that are left below videos, e.g:

    - https://symfonycasts.com/se...
    - https://symfonycasts.com/se...

    I hope this helps!

    Cheers!

  • 2019-10-15 Dung Le

    Victor Bocharsky Thanks Victor, my question is: does symfonycasts have any tutorial for such custom form and sql queries operations (inside Repository) FULL OUTER JOIN / INNER JOIN / LEFT JOIN etc from database? Thank you very much!

  • 2019-10-15 Victor Bocharsky

    Hey Dung,

    So, first of all you probably have 2 entities, i.e. Student and Address. And those entities should have some relations, like for example Student may have many Addresses, i.e. the relation is One to Many. So, what relation does your Student and Address entities have? Depends on this, you would need to use different form types, but most probably you would need to create a custom form type for both Student and Address types to specify what properties of those entities you want to see in the final form.

    Cheers!

  • 2019-10-14 Dung Le

    Hi Ryan, yes I still have this question. Thank you!

  • 2019-10-14 weaverryan

    Hey Dung Le!

    Do you still have this question? I noticed you asked it on a different thread, but removed the comment there. Let me know!

    Cheers!

  • 2019-10-13 Dung Le

    Good Morning, I have a hard time locating a tutorial where we can create an EDIT FORM with rows of fields where fields are retrieved from sql queries operations FULL OUTER JOIN / INNER JOIN / LEFT JOIN etc from database. take an example I have 2 tables STUDENT and ADDRESS, in an EDIT FORM in order to edit the address of a student I want to be able to see his/her first name and last name so that I can correctly identify the address belongs to them. How can I make such form please?

  • 2019-07-16 weaverryan

    Yo halifaxious !

    Sorry for my slow reply on this one! I might (or might not) have something that would help. Basically, instead of using the query_builder option, it's total legal to query (however you want) for the final array of AbstractStoragePlaceProxy objects (sorry if I've got the entity wrong) that you want in the drop-down and pass that as the choices option. That gives you full control over how you query for the entities, but you still get the nice things where it correctly renders the select tag with the ids and hydrates whatever is chosen back into an object.

    To actually query for the entities you need, I would add a __construct() to your form class and autowire whatever repository you want (it looks like maybe the LocationDetailViewRepository might be the most useful (or you could autowire the EntityManagerInterface). Then, use that below in buildForm to query for the entities you need and pass to the choices option. No query_builder option needed.

    Let me know if that helps!

    Cheers!

  • 2019-07-09 halifaxious

    Is there a way to setup EntityType so that I can query one class of entity but return a different class?

    I have Location chooser based on a complex set of inherited entities (Site->Room->Sublocation are all the same parent class). If I query for a location list using these entities, getting the 'choice_label' values adds up to a ridiculous number of queries since each choice object calls a method that does another query. So I created a read-only view in my db along with an associate entity called `LocationDetailView` which has all the properties for a choice in one row. Terrific! Except that now I have to transform my `LocationDetailView` object into a `Location` object when it gets submitted. And so far as I can see, that means that I can't use an EntityType because the query_builder needs the class property to equal `LocationDetailView` while the form itself needs the class property to equal `Location`.

    Working code with stupid number of queries (between 2 and 4 per entity depending on subtype):


    public function configureOptions(OptionsResolver $resolver)
    {
    $resolver->setDefaults(array(
    'class' => AbstractStoragePlaceProxy::class,
    'query_builder' => function(EntityRepository $er) {
    $qb = $er->createQueryBuilder('l');
    $qb
    ->leftJoin(LocationDetailView::class, 'v', Join::WITH, 'l.id = v.id')
    ->orderBy('v.site')
    ->addOrderBy('v.sortnum')
    ->addOrderBy('v.sublocation');


    if(!$this->authorizationChecker->isGranted('ROLE_ROOT')){
    $userEntity = $this->tokenStorage->getToken()->getUser()->getUserEntity();
    $rer = $this->em->getRepository(Room::class);
    $rids = $rer->getMyRoomIds($userEntity, DelegateInterface::FULLACCESS);
    $qb
    ->where($qb->expr()->in('v.rid', ':rids'))
    ->setParameter('rids', $rids);
    }
    return $qb;
    },
    'choice_label' => function($location){
    return $location->getPath();
    },
    'group_by' => function($val, $key, $index){
    $place = $val->getPlace();
    $site = $place instanceof SubLocation ? $place->getRoom()->getSite() : $place->getSite();
    return $site->getSiteAlias();
    }
    ));
    }

    Code that displays but won't submit due to wrong entity type:


    public function configureOptions(OptionsResolver $resolver)
    {


    $resolver->setDefaults(array(
    'class' => LocationDetailView::class,
    'query_builder' => function(EntityRepository $er) {
    $qb = $er->createQueryBuilder('v');
    $qb
    ->orderBy('v.site')
    ->addOrderBy('v.sortnum')
    ->addOrderBy('v.sublocation');


    if(!$this->authorizationChecker->isGranted('ROLE_ROOT')){
    $userEntity = $this->tokenStorage->getToken()->getUser()->getUserEntity();
    $rer = $this->em->getRepository(Room::class);
    $rids = $rer->getMyRoomIds($userEntity, DelegateInterface::FULLACCESS);
    $qb
    ->where($qb->expr()->in('v.rid', ':rids'))
    ->setParameter('rids', $rids);
    }
    return $qb;
    },
    'choice_label' => function(LocationDetailView $location){
    return trim(join('/',[$location->getRnum(),$location->getSublocation()]), '/');
    },
    'group_by' => function(LocationDetailView $location, $key, $index){
    return $location->getSite();
    }
    ));
    }
  • 2019-06-11 Diego Aguiar

    Hey Ionut Stoican

    A quick and easy way would be to handle both forms in the same controller's action. It's a bit ugly but you would save some troubles because of validations. If you split your submit actions into different routes then it would be somehow hard to print the form errors on the same page, probably would be better to handle it with some javascript code.

    Cheers!

  • 2019-06-08 Ionut Stoican

    How would I go if I wanted for example to have a registration and a login form on the same page?