Flag of Ukraine
SymfonyCasts stands united with the people of Ukraine
This tutorial has a new version, check it out!

Custom Query in EntityType

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

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:

... 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:

... 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:

... 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:

... 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:

... 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:

... 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():

... 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:

... 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!

51
Login or Register to join the conversation
Default user avatar
Default user avatar diarselimi92 | posted 4 years ago

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 ?

41 Reply

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!

Reply
Tim K. Avatar

Hello SymfonyCast-Team,

First:

you guys are doing a great job! All your videos are so right to the spot. Thanks!

Remark:

I am using Symfony 5.2, but I found no better video to place my question.

My question:

I want to have a select-box for an event. In this select-box there are a couple of persons from which the user can take a choice. But there should only the persons in that list that are active. Additionally I need the person that is currently allocated to the event, but that could probably be inactive in the meantime.

In general I know how to solve this with the query_bilder (see below). But I don't know how to get the id of that current person. In other FormTypes I used $option['data'], but in this case I don't have data.

Why not? How can I solve this?

Thanks for any support!
Tima

SUB-FORM


class EventFormType extends AbstractType {
public function buildForm(FormBuilderInterface $builder, array $options) {


/** @var Event $event */
$event = $options['data']; // This is what I need, but is NULL


$currentLecturerId = $event->getLecturer()->getId(); // This how it should work
$currentLecturerId = 10; // DUMMY to test the query_builder


$builder
[...]
->add('lecturer', EntityType::class, [
'label' => 'Lecturer',
'class' => Lecturer::class,
'query_builder' => function (LecturerRepository $rep) use ($currentLecturerId) {
return $rep->createQueryBuilder('l')
->andWhere('l.active = true OR l.id = :lecturerId')->setParameter('lecturerId', $currentLecturerId)
->addOrderBy('l.lastName', 'asc')
->addOrderBy('l.firstName', 'asc');
},
'required' => true,
'attr' => [
'class' => 'form-control-sm'
],
])
[...]
}
}

MAIN-FORM


class CourseModalEventFormType extends AbstractType {


public function buildForm(FormBuilderInterface $builder, array $options) {
$course = $options['data'];
dump($course); // this is working


$builder
->add('events', CollectionType::class, [
'label' => 'x1x2 (dummy)',
'entry_type' => EventFormType::class,
'entry_options' => [
'label' => false,
],
'by_reference' => false,
'allow_add' => true,
'allow_delete' => true,
])
->add('save', SubmitType::class, [
'attr' => [ 'class' => 'btn-success'],
])
;
}
}

CONTROLLER:


[...]
$formCourseModalEvents = $this->createForm(CourseModalEventFormType::class, $course, [
'action' => $this->generateUrl('app_course_events_changed', ['courseId' => $courseId]),
]);
[...]
Reply

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!

Reply
Tim K. Avatar

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

Reply

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!

Reply
Abelardo L. Avatar
Abelardo L. Avatar Abelardo L. | posted 3 years ago

Hi everyone!
I have following this lesson in order to retrieve information from a database (where else? :D ).

I have got a ClientType (Client entity's form). Inside this form, I have to retrieve a collection of bookingTeam ids.

I have coded these lines:

->add('bookingTeamID',EntityType::class,
[
'class' => BookingTeam::class,
'query_builder' =>function(BookingTeamRepository $repo) {
return $repo->findAllBookingTeamsAlphabeticalBy(); // below detailed
},
'choice_label' => function(BookingTeam $bookingTeam) {
return sprintf('%s', $bookingTeam->getTeamName());
},
'required'=>true,
'placeholder' => 'Choose a booking team'
]
)

WRONG: I had got this method BEFORE
public function findAllBookingTeamsAlphabeticalBy() {
return $this->createQueryBuilder('b')
->orderBy('b.teamName', 'ASC')
->getQuery()
->execute()
;
}

FIXED: I have got this method NOW
public function findAllBookingTeamsAlphabeticalBy() {
return $this->createQueryBuilder('b')
->orderBy('b.teamName', 'ASC');
}

When I run Symfony, the following message is given:
Expected argument of type "Doctrine\ORM\QueryBuilder", "array" given

What's wrong?

Since I have followed your steps, I consider it could come from Symfony version: got 3.3.2.

Any help would be welcome.

Brs. :)

Reply
Abelardo L. Avatar

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.

Reply

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!

Reply
Michael K. Avatar

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!

Reply

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/fu...

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!

Reply
Michael K. Avatar
Michael K. Avatar Michael K. | victor | posted 4 years ago

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!

Reply

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!

Reply
Michael K. Avatar

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!

Reply

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

Reply
Default user avatar
Default user avatar Marco La Cugurra | posted 4 years ago

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 :)

Reply

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!

Reply
Default user avatar
Default user avatar Marco La Cugurra | victor | posted 4 years ago

Thanks Victor, yes now it's working :)

59 Reply
Default user avatar

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

Reply

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!

1 Reply
Default user avatar
Default user avatar Blueblazer172 | posted 4 years ago

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.

Reply

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!

Reply
Default user avatar
Default user avatar Blueblazer172 | victor | posted 4 years ago

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

Reply

Wow, fast fix! Great! ;)

Cheers!

Reply
Default user avatar

Catchable Fatal Error: Object of class AppBundle\Entity\Tblcountry could not be converted to string
I tried to remove {{ form_row(edit_form.countryname, { 'label': 'Country *' }) }} in view, but problem is happening before view :(
Can someone help?

this is my query builder:
createQueryBuilder('tblcountry')
->orderBy('tblcountry.countryname', 'ASC');
return $data;
}
}

Reply

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!

Reply
Default user avatar
Default user avatar Macarena Paz Diaz Colomes | posted 4 years ago

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

Reply

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!

Reply
Default user avatar
Default user avatar Macarena Paz Diaz Colomes | victor | posted 4 years ago

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!

Reply

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!

Reply
Default user avatar
Default user avatar Macarena Paz Diaz Colomes | weaverryan | posted 4 years ago

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!

Reply

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

Reply
Cesar Avatar

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.

Reply

Hey Cesar!

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

Cheers!

Reply
Cesar Avatar

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?

Reply

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!

Reply
Cesar Avatar

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).

Reply

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!

Reply
Default user avatar
Default user avatar Roel Beckers | posted 4 years ago

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

Reply

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!

Reply
Default user avatar

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

Reply

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!

Reply
Default user avatar

Greetings.

Thanks for this!
I'd built a sort of generic drop-down for an entity in which the appropriate querybuilder is determined by a form option. The form field originally had a set of querybuilders, but they really needed to be in a repository. This tutorial showed me how. The field now looks like this:


$builder
->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);
}
return $repo->getAllSelectable();
},
'mapped' => false,
]
)
Reply

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!

Reply
Default user avatar

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,
]
)
Reply

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!

Reply
Michael K. Avatar
Michael K. Avatar Michael K. | posted 4 years ago

Hi

After a loooong search in the web, I ask you for help ;-)
I have build a custom query in entity type, it works fine ;-) Is it possible to add a "data-options", "class" or "style" to every options of the select?
My idea: with the select I want to select some labels. Every label-entity has a field name and color. I would be cool, when every options could have his color.

Cheers!
Michael

Reply

Hey Michael K.

Such a nice thing you want to do :)
The first thing I can think of is doing it via "choice_attr" option, is not the best way, because your presentation logic will live inside a FormType, but as always, is good to get things done first, and then take some time to improve them.

You can read more about "choice_attr" here: http://symfony.com/doc/curr...

Cheers!

Reply
Michael K. Avatar

Hi Diego

Thanks a lot for the fast solution! Now I can give every option an other class. But I noticed, that maybe it isn't possible to style the options in every browser. Only in FireFox the css works fine :-/

Is it possible to build the options as a list?

Best regards!
Michael

Reply

Yeah, that's the problem with select options =/
What you ask (I believe) is possible, but it would involve a lot of work.
I think, your best choice is to add those values via data attributes (As you already did), and then style it with JS. You might find this JS plugin helpful https://selectize.github.io...

Cheers!

Reply
Cat in space

"Houston: no signs of life"
Start the conversation!

What PHP libraries does this tutorial use?

// composer.json
{
    "require": {
        "php": ">=5.5.9",
        "symfony/symfony": "3.1.*", // v3.1.4
        "doctrine/orm": "^2.5", // v2.7.2
        "doctrine/doctrine-bundle": "^1.6", // 1.6.4
        "doctrine/doctrine-cache-bundle": "^1.2", // 1.3.0
        "symfony/swiftmailer-bundle": "^2.3", // v2.3.11
        "symfony/monolog-bundle": "^2.8", // 2.11.1
        "symfony/polyfill-apcu": "^1.0", // v1.2.0
        "sensio/distribution-bundle": "^5.0", // v5.0.22
        "sensio/framework-extra-bundle": "^3.0.2", // v3.0.16
        "incenteev/composer-parameter-handler": "^2.0", // v2.1.2
        "composer/package-versions-deprecated": "^1.11", // 1.11.99
        "knplabs/knp-markdown-bundle": "^1.4", // 1.4.2
        "doctrine/doctrine-migrations-bundle": "^1.1" // 1.1.1
    },
    "require-dev": {
        "sensio/generator-bundle": "^3.0", // v3.0.7
        "symfony/phpunit-bridge": "^3.0", // v3.1.3
        "nelmio/alice": "^2.1", // 2.1.4
        "doctrine/doctrine-fixtures-bundle": "^2.3" // 2.3.0
    }
}