Getting error on NOT IN subquery in Doctrine (Symfony 4)

Question

I'm trying to convert this MySql query

SELECT appcs_training.name, appcs_training.id FROM appcs_training WHERE appcs_training.id NOT IN (SELECT training_id FROM appcs_user_purchased_trainings WHERE user_id = 54)

into a queryBuilder but I am getting the following error over and over again... I would appreciate your help to solve this.

[Semantical Error] line 0, col 57 near 'training FROM': Error: Invalid PathExpression. Must be a StateFieldPathExpression.

And this is my code:

    public function findNonPurchasedTrainingsByUserId($userId)
{
    $expr = $this->em->getExpressionBuilder();
    return $this->em->createQueryBuilder()
        ->select('t')
        ->from('App:Training', 't')
        ->where($expr->notIn(
            't.id',
            $this->em->createQueryBuilder()
                ->select('ut.training')
                ->from('App:UserTraining', 'ut')
                ->where('ut.user = :userId')
                ->setParameter('userId', $userId)
                ->getDQL()
        ))
        ->getQuery()
        ->getResult();
}

Hi, does this answer your question: https://stackoverflow.com/questions/10762586/how-to-convert-this-to-doctrine-2-querybuilder-format/10763358#10763358 — GrenierJ, Feb 12 '20 at 07:43
Hi, I am using Symfony 4.3.8. I tried to implement @GrenierJ´s answer, but got this error: 'Too few parameters: the query defines 1 parameters but you only bound 0' — Yurniel LAHERA VILLA, Feb 12 '20 at 12:42
Yes, the method accepts the user id as parameter. Well, i am not sure whether I have to use ':userId' or 'userId' on setParameter() — Yurniel LAHERA VILLA, Feb 12 '20 at 14:35

score 0 · Answer 1 · answered Feb 13 '20 at 05:42

Finally, setParameter wasn't valid. This is the only way I made it work

public function findNonPurchasedTrainingsByUserId(int $userId)
{
    $expr = $this->getEntityManager()->getExpressionBuilder();

    $subQuery = $this->em->createQueryBuilder()
        ->select('tr.id')
        ->from('App:UserTraining', 'ut')
        ->leftJoin('App:Training', 'tr', Join::WITH, $expr->eq('ut.training', 'tr.id'))
        ->where($expr->eq('ut.user', $userId))
        ->getDQL()
    ;

    $query = $this->em->createQueryBuilder();
    $query->Select('t')
        ->from('App:Training', 't')
        ->where($query->expr()->notIn('t.id', $subQuery))
        ->andWhere($query->expr()->eq('t.isActive', true))
        ->setMaxResults(8)
    ;
    return  $query->getQuery()->getResult();
}

Getting error on NOT IN subquery in Doctrine (Symfony 4)

1 Answers1

Linked