Symfony2+Doctrine2: Error in QueryBuilder

Question

I am using symfony2 and doctrine2 to develop a web app.

I have a form in the app that has a dropdown that is populated from an entity. I have configured a query_builder to filter the values in the dropdown field.

public function buildForm(FormBuilderInterface $builder, array $options) {
    $centerId = $this->centerId;

    $builder->add("glCode", "entity", array(
        "class" => "MyBundle:GlCode",
        "query_builder" => function(EntityRepository $er) use($centerId) {
            return $er->createQueryBuilder("g")
                ->join("g.account", "a")
                ->where("g.id NOT IN (SELECT g2.id FROM MyBundle:OtherFixedCost c JOIN MyBundle:GlCode g2)")
        }
    ));

This code is producing the error: Expected Doctrine\ORM\Query\Lexer::T_WITH, got ')'

I am trying to do with the query builder the same thing I would do with the following DQL:

SELECT g FROM MyBundle:GlCode g
JOIN g.account a
WHERE g.id NOT IN (
   SELECT g2.id FROM MyBundle:OtherFixedCost c INNER JOIN MyBundle:GlCode g2
)

I do not know if I am missing something, but apparently doesn't exist a way to use directly DQL in the form class. So, I am being forced to use QueryBuilder, but I am getting the above error.

score 0 · Answer 1 · edited May 23 '17 at 11:46

The problem is in your where clause, you are supposed to use the function notIn() instead of just writing everything on DQL. Take a look at the documentation

Maybe this and this could help, the second is actually quite similar to your query, I quote some code from there:

$qb->select('l')
   ->from('Entity\Location', 'l')
   ->where('l.state = :state')
   ->setParameter('state', 'UT')
   ->andWhere($qb->expr()->notIn('u.id', 
       $qb->select('l2.id')
          ->from('Entity\Location', 'l2')
          ->where(l2.location_type = ?1 AND l2.population < ?2)
          ->setParameters(array(1=> 1, 2 => 1000))
));

Have a look on how the subquery is built.

score 0 · Answer 2 · answered May 03 '13 at 01:04

Subqueries should join entities via alias, not directly to entity. Ie.

(SELECT g2.id FROM MyBundle:OtherFixedCost c JOIN MyBundle:GlCode g2)

try

(SELECT g2.id FROM MyBundle:OtherFixedCost c JOIN c.GlCodes g2)

or whatever the relation name between OtherFixedCost and GlCode is. Maybe this will help, I write subqueries like this all the time and no problems.

Symfony2+Doctrine2: Error in QueryBuilder

2 Answers2