原则2-如何在多个addSelect中添加限制

Question

问题：运行查询时出现错误

{
"status": false,
"error": {
"classname": "Doctrine\ORM\Query\QueryException",
"message": "[Syntax Error] line 0, col 783: Error: Expected Doctrine\ORM\Query\Lexer::T_CLOSE_PARENTHESIS, got 'LIMIT'"
}
}

如何在下面的查询中添加addSelect中的限制。

$qb = $this->em->createQueryBuilder();
$qb->select("identity(igsm.group) as group_id,
qp.id AS q_paper_id,
qp.name AS q_name,
qp.description AS q_description,        
qp.marks,
identity(qp.questionPaperStatus) AS qpStatus,
qp.timeInMinutes as time_in_minutes,
sa.marks as marks_obtained,
sa.id AS assessment_id");
$qb->addSelect("(SELECT IfElse(count(qps.id) > 0 , TRUE, FALSE) FROM EntityQuestionPaper qps "
. "WHERE qps.createdBy = :userId AND qps.id = qp.id) as flag");
$qb->addSelect("(SELECT count(qpe.id) from EntityQuestionPaperEvaluation qpe "
. "where qpe.questionPaper = qp.id AND qpe.user = :userId) as student_evaluation_cnt");
$qb->addSelect("(SELECT LOWER(ats.assignee) from EntityAssessmentTimelineStatus ats, EntityAssessmentStudentTimeline ast "
. "WHERE ast.assessmentTimelineStatus = ats.id AND ast.user = igsm.user AND ast.assessment = qp.id "
. "ORDER BY ats.id DESC LIMIT 1) AS assignee");
$qb->addSelect("(SELECT identity(ast.assessmentTimelineStatus) from EntityAssessmentTimelineStatus ats, EntityAssessmentStudentTimeline ast "
. "WHERE ast.assessmentTimelineStatus = ats.id AND ast.user = igsm.user AND ast.assessment = qp.id "
. "ORDER BY ats.id DESC LIMIT 1) AS assessment_timeline_id");
$qb->addSelect("(SELECT UPPER(ConcatWs(' ', ats.action,ats.item)) as assesment_submissions from EntityAssessmentTimelineStatus ats, "
. "EntityAssessmentStudentTimeline ast WHERE ast.assessmentTimelineStatus = ats.id AND ast.user = igsm.user AND ast.assessment = qp.id "
. "ORDER BY ats.id DESC LIMIT 1) AS action");
$qb->from('EntityQuestionPaperGroupStudentMap', 'qpgsm');
$qb->innerJoin('EntityInstituteGroupStudentMap', 'igsm', 'WITH', 'igsm.id=qpgsm.instituteGroupStudentMap');
$qb->innerJoin('EntityQuestionPaper', 'qp', 'WITH', 'qp.id=qpgsm.questionPaper AND qp.questionPaperStatus > 4');
$qb->leftJoin('EntityAssesmentSubmissions', 'sa', 'WITH', 'sa.assesmentId = qp.id AND sa.userId = igsm.user');
$qb->leftJoin('EntityPackageQuestionPaperHistory', 'pqph', 'WITH', 'pqph.questionPaperId = qp.id');
$qb->where('igsm.user = :userId')->setParameter(':userId', $userId);
$qb->andWhere("igsm.group IN (:GroupIds)")->setParameter(':GroupIds', explode(",", $groupId));
$qb->orderBy('qpgsm.id', 'DESC');
$qb->setMaxResults(1);
$result = $qb->getQuery()->getResult();
return $result;

虽然子查询依赖于主查询，但我可以单独放置子查询吗。如何在子查询中添加限额。

谢谢。

Answer 1

github中存在一个关于此的问题。

同样的问题也被问到了：

• 条令中带LIMIT的子查询
• 条令2限制IN子查询

一个可能的快速破解解决方案可以是：

• 用select语句中的"order by"列来准备所需的select列
• 使用MIN或MAX聚合结果，只得到第一个结果(与LIMIT 1的结果相同)

此子选择：

$qb->addSelect("(SELECT LOWER(ats.assignee) from EntityAssessmentTimelineStatus ats, EntityAssessmentStudentTimeline ast "
. "WHERE ast.assessmentTimelineStatus = ats.id AND ast.user = igsm.user AND ast.assessment = qp.id "
. "ORDER BY ats.id DESC LIMIT 1) AS assignee");

将写为：

$qb->addSelect("(SELECT MAX(concat(ats.id, ' ', LOWER(ats.assignee))) from EntityAssessmentTimelineStatus ats, EntityAssessmentStudentTimeline ast "
. "WHERE ast.assessmentTimelineStatus = ats.id AND ast.user = igsm.user AND ast.assessment = qp.id "
. "ORDER BY ats.id DESC) AS assignee");

当您获得"assignee"值时，您可以拆分为第一个空白值，并获得"LOWER(ats.assignee)"的值。

$result = explode(' ',$assignee, 2);

ORDER BY为DESC时使用MAX，ASC时使用MIN。

这是因为MAX或MIN聚合函数只选择最高或最低值，该值可以是数字或字符串。

参考

MAX/MIN mysql文档

分解为第一个空白字符