Doctrine中NOT IN子查询出错(Symfony 4) [英] Getting error on NOT IN subquery in Doctrine (Symfony 4)

问题描述

我正在尝试转换此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();
}

这篇关于Doctrine中NOT IN子查询出错(Symfony 4)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！