Symfony: How to execute raw SQL which returns objects inside Form

Ever wanted to fetch your entities using complex query from inside you form class? Check out below.

All code was tested on Symfony 5, but after small changes (or even without) it should work also in Symfony 4.

So we’re starting with injecting Entity Manager to our form:

use Doctrine\ORM\EntityManagerInterface;
use Doctrine\ORM\Query\ResultSetMappingBuilder;
class MyFormType extends AbstractType
{
    private $entityManager;
    public function __construct(EntityManagerInterface $entityManager)
    {
        $this->entityManager = $entityManager;
    }
}

Next in buildForm method, just fetch required data (in our example we’re fetching from JSON type field):

public function buildForm(FormBuilderInterface $builder, array $options)
{

    $str = "SELECT * FROM backend_user WHERE JSON_CONTAINS(roles, '[\"" . BackendRoles::ROLE_CUSTOM . "\"]')";

    $rsm = new ResultSetMappingBuilder($this->entityManager);
    $rsm->addRootEntityFromClassMetadata('App\Entity\BackendUser', 'alias');
    $query = $this->entityManager->createNativeQuery($str, $rsm);
    $objects = $query->getResult();
    $builder->add('selectedDealer', EntityType::class, [
        'class' => BackendUser::class,
        'placeholder' => '- choose --',
        'choices' => $objects,
        'choice_label' => 'username',
    ]);
}

And thats it. Beware that using ‘query_builder’ property as shown in Symfony doc, you need to return QueryBuilder object which makes it impossible to execute raw sql.