Custom Query in EntityType

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!

Hey @Zane!

Over in SO, you mentioned a "stop gap" where you query for the Opportunity and pass it the form. I think this is actually the correct solution. Usually, you know (when creating the form object) whether or not you are creating a new object or updating an existing one. If you want a new one, you don't pass the form any object. And if you are editing an existing one, you query for that and pass it to createForm(). If I understand correctly, your situation is a bit unique because you want the form to dynamically switch between a new/update based on the value of a submitted field. There is probably some fancy way for the form to do this automatically, but I would solve it exactly like you did. Another option is to not bind your form to the Opportunity entity at all. Then, in your controller, grab the array of data via $form->getData() then query for the Opportunity or create a new one if it's not found.

Cheers!

Hi Ryan! Thanks for you reply! I feel much better now!

p.s. You don't know me, but I learned Symfony back in 2018 following Sfcast for many, many hours. Since then, you're one of my superhero and developer role model ;-)

Ah, that's fantastic! Thanks for popping in with the kind words - so cool 5 years later you're crushing it. Keep up the good work!

Hey Tim,

Yeah, that's because that form type is "subtype", so it's a bit tricky :) You need pass the data you need explicitly. First, in your EventFormType, add implement this method:

class EventFormType
{
    public function configureOptions(OptionsResolver $resolver)
    {
        $resolver->setRequired('course');
    }
}

This will always require you passing the course option to that form type. But to pass it from the CourseModalEventFormType, you will need to change your code to:

class CourseModalEventFormType
{
    public function buildForm()
    {
        $course = $options['data']; 

        $builder
            ->add('events', CollectionType::class, [
                // ...
                'entry_options' => [
                    'course' => $course,
                ],
            ])
    }
}

Then, in EventFormType you will able to get access to it via "$options['course']", try to dump this instead :)

I hope you got the idea!

Cheers!

Hello Victor,

many thanks for your response and the time for helping me. Very much appreciated!

Actually I found in parallel a similar solution by using

//...
$resolver->setDefaults([
    'data-class' => Event::class,
    'course'       => null,

]);

instead of your proposal

//...
$resolver->setRequired('course');

I guess the only difference is that one is optional and the other mandatory?

Anyways... both solutions/workarounds are giving me all Events of a course and not that one Event. The only difference is that I have now to extract and pass an array of potential "lectureIDs" (and not only that one "lectureID"). Thats OK for me now. But I am curious if it would be still possible to grap that one Event?

Cheers!

Tim

Hey Tim,

Yes, setRequired() forces you always passing a value for that "course" - good idea when you always need that value to be passed.
Hm, I suppose you can pass the proper event the same way as you did for that course value.

Cheers!

I am going to reply myself in order to be queried by another people.

The solution is under the FIXED section inside my above own comment.

Best regards.

Hi Ryan
I also need a custom query for related records and I have read your post. Passing a "user" from the controller to the form works fine. But I don't understand how I can get "user" in the $builder.

In my class StudentFormType :

    public function buildForm(FormBuilderInterface $builder, array $options)
    {
        $builder
            ->add('competitiongroup', EntityType::class, [
                'class' => CompetitionGroup::class,
                'query_builder' => function(CompetitionGroupRepository $repo) {
                    return $repo->findAllCompetitionGroupByUser($options['user']);
                },
            ])

$options['user'] shows me an error.

The other question is, I don't need the object "user" for my query, I need the "event" from the controller. Is ok, when I pass another object to the option "user"?

        $form = $this->createForm(StudentFormType::class, $student, [
            'user' => $event,
        ]); 

Thanks a lot for your help!

Hey Michael,

Actually, that's a PHP restriction, you have a different scope in anonymous function, that's why you can't get access to $options var by default... to allow access a variable which is defined outside of anonymous function - use "use($options)" feature:

    public function buildForm(FormBuilderInterface $builder, array $options)
    {
        $builder
            ->add('competitiongroup', EntityType::class, [
                'class' => CompetitionGroup::class,
                'query_builder' => function(CompetitionGroupRepository $repo) use ($options) {
                    return $repo->findAllCompetitionGroupByUser($options['user']);
                },
            ]);

You can read more about anonymous functions in PHP docs: http://php.net/manual/en/functions.anonymous.php

About your second question - you can... but that sounds weird. To avoid mess, better rename this variable to event:

        $form = $this->createForm(StudentFormType::class, $student, [
            'event' => $event,
        ]); 

and then in the form tweak it too:

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
        $event = $options['event'];
    }

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

That's much better ;)

Cheers!

Hi Victor

Thank you very much for the fast reply!
Now I have this error: "Expected argument of type "Doctrine\ORM\QueryBuilder", "array" given"
In my Repo I have this query:

public function findAllCompetitionGroupByEvent($options)
{
return $this->createQueryBuilder('competitiongroup')
->where('competitiongroup.event = :event')
->setParameter('event', $options)
->orderBy('competitiongroup.name', 'ASC')
->getQuery()
->execute();
}
Must I use a SQL query instead of the QueryBuilder?

Second question:
With:
class StudentFormType extends AbstractType
{
public function buildView(FormView $view, FormInterface $form, array $options)
{
$event = $options['event'];
}
public function buildForm(FormBuilderInterface $builder, array $options)
{
$builder...

I get this error: " Decleration must be compatible with FormTypeInterface->buildView..."

Sorry, for my Newbie-Questions ;-)
Thanks a lot for you help!

Hey Michael K.

For your first question: I suggest you to use QueryBuilder every time it makes your life easier, because when you execute it, you don't have to hydrate your objects and that makes your Repository methods more re-usable.
For the error you are experimenting, can you show me the full error with stack trace ? I'm guessing you need to return the QueryBuilder instead of the executed query.

For your second question: The same, can you show me the full error with stack trace. I believe you using the wrong use statement

Cheers!

Hi Diego

With:
public function findAllCompetitionGroupByEvent($options)
{return $this->createQueryBuilder('competitiongroup')
->where('competitiongroup.event = :event')
->setParameter('event', $options)
->orderBy('competitiongroup.name', 'ASC')
}
it works perfect! :-)

And for the second question, you're also right! I had only to add the two use statement: FormInterface and FormView

Thank you very much for your patience for my beginner questions!
Cheers!

No problem, I'm glad to help :) and welcome to the Symfony world!

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!

Thanks Victor, yes now it's working :)

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!

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!

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

Wow, fast fix! Great! ;)

Cheers!

Hey Daka

In some where you are trying to use your Tblcountry object as a string, maybe in your FormType, could you show it to me?

Cheers!

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!

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!

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!

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!

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

Hey Cesar!

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

Cheers!

Diego, my question is about how to custom a query in the EntityType using DBAL? Have you tried? It's related about this chapter using DBAL. If you try to call $this->getEntityManager()->getConnection() it will appear an error. Can you help me?

Hey Cesar!

I think I can help... at least partially :). Here's what you need to do:

A) Setup your EntityType with a query_builder option like normal. Call some new method on your repository (that we will create in a moment):

            ->add('subFamily', EntityType::class, [
                'query_builder' => function(SubFamilyRepository $repo) {
                    return $repo->getMyReallyCustomQueryBuilder();
                }
            ])

B) Next, we need to create the getMyReallyCustomQueryBuilder() method in your repository. Here is the tricky part: the EntityType needs a QueryBuilder object... so you can't use a raw SQL query (as it returns raw result set). However, we can mix the two ideas: write a custom, complex SQL query to return an array of ids, then create a QueryBuilder that fetches those ids:

// in SubFamilyRepository.php
public function getMyReallyCustomQueryBuilder
{
        $conn = $this->getEntityManager()
            ->getConnection();
        // a complex query that selects the id 
        $sql = 'SELECT id FROM ...';
        $stmt = $conn->prepare($sql);
        $stmt->execute(array());
        $ids = [];
        foreach ($stmt->fetchAll() as $row) {
            $ids[] = $row['id'];
        }

        return $this->createQueryBuilder('sub_family')
            ->andWhere('sub_family.id IN (:ids)')
            ->setParameter('ids', $ids);
}

That code may not be exactly right, but hopefully it gives you an idea: use custom SQL to get the ids you need, then return a query builder that only fetches those ids.

Cheers!

Thanks Ryan! I will try your recommendation.
All your courses use Doctrine and I keep thinking if I need to learn it because I have used SQL for many years and I don't see the point to learn an ORM adding more complexity when I don't need it (at least apparently).

Hey Cesar!

Yea, in theory, you can do a lot with SQL... but so many tools and practices expect you to retrieve *objects* from the database (not just result sets)... so if you use raw SQL too much, you ultimately work against many of these things and make your life more difficult! But, it's still a good option to use from time-to-time :).

Cheers!

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!

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

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!

Hey geoB

That's an interesting use case. Just for your information, you could have done it in another way, where all that logic for determining which repository to use would lived in the controller's action, and then, just pass the choice options to the "form type". But, hey, your solution goods great too :)

Cheers!

Glad you find it interesting. In this use case it's useful to have both a queryBuilder instance and query results. The controller renders a flash message if the results are empty and the queryBuilder instance is used in the form type if not empty. Here's an example of what it looks like:

Controller snippet:

        $artistsQuery = $em->getRepository('AppBundle:Artist')->deleteableArtists();
        $artists = $em->getRepository('AppBundle:Artist')->processQuery($artistsQuery);
        $flash = $this->get('braincrafted_bootstrap.flash');
        if (empty($artists)) {
           $flash->info('All artists have some sales');

           return $this->redirectToRoute('homepage');
        } else {
            return $this->redirectToRoute('artist_select', ['target' => 'delete']);
        }

Repository snippet:

    public function deleteableArtists()
    {
        return $this->createQueryBuilder('a')
                ->join('AppBundle:Ticket', 't', 'WITH', 't.artist = a')
                ->groupBy('a.lastName')
                ->groupBy('a.firstName')
                ->having('SUM(t.amount) = 0')
        ;
    }

    public function processQuery($query)
    {
        return $query->getQuery()->getResult();
    }

Form type snippet:

            ->add(
                'artist', EntityType::class,
                [
                    'class' => Artist::class,
                    'label' => false,
                    'choice_label' => function ($artist, $key, $index) {
                        return $artist->getLastName() . ', ' . $artist->getFirstName();
                    },
                    'query_builder' => function (ArtistRepository $repo) use ($target, $notId, $show) {
                        if ('replacement' === $target) {
                            return $repo->getReplacableArtists($notId);
                        }
                        if ('block' === $target || 'tickets' === $target) {
                            return $repo->getBlockOrTickets($show);
                        }
                        if ('delete' === $target) {
                            return $repo->deleteableArtists();
                        }
                        return $repo->getAllSelectable();
                    },
                    'mapped' => false,
                ]
            )

Ohh, nicely done geoB, thanks for sharing it :)

By the way, you can change this

$em->getRepository('AppBundle:Artist')```

to this: 

$em->getRepository(Artist::class)`

It's useful when you decide to rename your entity class name by using a refactoring tool

Cheers!