Cannot getEntityManager from repository in form builder

In a form type in Symfony2, I want to include an entity field with a query builder in order to select which rows to show. My query is complicated and I cannot figure out a way to make it with the query builder, I would like to use DQL. Unfortunately, I can't do $repository->createQuery(...) nor $repository->getEntityManager()->createQuery(). Only $repository->createQueryBuilder(...) works. Is there a way around this?

BTW, if you happen to find a smarter way to perform my SQL request that would be very useful (but that is not the topic of this question!)

public function buildForm(FormBuilderInterface $builder, array $options)
{
    $user = $this->user;
    $builder
        ->add('friend', 'entity',
            array(
                'class' => 'MyappUserBundle:User',
                'label' => 'User',
                'query_builder' => function(EntityRepository $repository) use ($user) {
                    $query = $repository->getEntityManager()->createQuery("
                        SELECT u
                            FROM MyappUserBundle:User u
                        WHERE
                            u.id NOT IN (SELECT friend FROM MyappUserBundle:Friendrequest WHERE user = :user)
                        AND 
                            u.id NOT IN (SELECT user FROM MyappUserBundle:Friendrequest WHERE friend = :user)
                        AND 
                            u.id != :user
                        ")
                        ->setParameter('user', $user)
                        ;

                    return $query;
                }
                )
            )
        ;
}

How to solve query_builder problems (and problems in general)

We know we have access to the EntityRepository class of your entity. Therefore we can easily find what methods is publicly accessible.

Therefore, we can search on Google "doctrine api entityrepository" which leads us to the EntityRepository class API documentation on the doctrine site.

On that page, we can find which method is publicly accessible, you can find 3 methods about creating queries and one of them is createNativeNamedQuery.

According to the documentation, this method returns an instance of Doctrine\ORM\NativeQuery, clicking on it leads to another page with other methods. You can see that NativeQuery has a setQuery method and inherits from Doctrine\ORM\AbstractQuery a list of other methods.

Now, we probably have all the information we need therefore we can come up with a solution that probably works (if it doesn't then we are probably really really close).

// The closure expects \Doctrine\ORM\QueryBuilder to be returned
// so we need to create a named query first then create a list of ids
// and pass it to a query builder.
'query_builder' => function(EntityRepository $repository) use ($user) {
    // Query for user ids with sub queries
    $results = $repository
        ->createNativeNamedQuery('u')
        ->setSQL("
            SELECT u.id
            FROM user AS u
            WHERE u.id NOT IN (
                SELECT friend FROM friend_request_table WHERE user = :user
            )
            AND u.id NOT IN (
                SELECT user FROM friend_request_table WHERE friend = :user
            )
            AND u.id != :user
        ")
        ->setParameter('user', $user->getId())
        ->getArrayResult();

    // Build an array of IDs
    $ids = array_map(function ($row) {
        return $row['id'];
    }, $results);

    // Returns a QueryBuilder
    return $repository->createQueryBuilder('u')
        ->where('u.id IN (:ids)')
        ->setParameter('ids', $ids);
}

The drawback is that two queries are executed. The closure expects QueryBuilder to be returned so we need to create a raw query first then pass the results to the QueryBuilder.

On top of that, native query means raw SQL queries therefore you can't use the entity annotation and also your queries may not be supported across different databases.

If you'd like my opinion, I think you can refactor your query and remove the subqueries. Removing the subqueries and passing their results as parameters will allow you to use DQL and therefore support more types of database.