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