Doctrine中NOT IN子查询出错(Symfony 4)

2022-09-04 00:00:00 php symfony doctrine-orm query-builder

我正在尝试转换此MySQL查询

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)

到queryBuilder中,但我一次又一次地收到以下错误... 如果您能帮我解决这个问题,我将不胜感激。

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

这是我的代码:

    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();
}

解决方案

最后,set参数无效。这是我成功的唯一方法

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();
}

相关文章