php - Doctrine 2 - 如何在多个 addSelect 中添加限制
问题描述
问题:运行查询时出错
{
"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 Entity\QuestionPaper qps "
. "WHERE qps.createdBy = :userId AND qps.id = qp.id) as flag");
$qb->addSelect("(SELECT count(qpe.id) from Entity\QuestionPaperEvaluation qpe "
. "where qpe.questionPaper = qp.id AND qpe.user = :userId) as student_evaluation_cnt");
$qb->addSelect("(SELECT LOWER(ats.assignee) from Entity\AssessmentTimelineStatus ats, Entity\AssessmentStudentTimeline 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 Entity\AssessmentTimelineStatus ats, Entity\AssessmentStudentTimeline 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 Entity\AssessmentTimelineStatus ats, "
. "Entity\AssessmentStudentTimeline 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('Entity\QuestionPaperGroupStudentMap', 'qpgsm');
$qb->innerJoin('Entity\InstituteGroupStudentMap', 'igsm', 'WITH', 'igsm.id=qpgsm.instituteGroupStudentMap');
$qb->innerJoin('Entity\QuestionPaper', 'qp', 'WITH', 'qp.id=qpgsm.questionPaper AND qp.questionPaperStatus > 4');
$qb->leftJoin('Entity\AssesmentSubmissions', 'sa', 'WITH', 'sa.assesmentId = qp.id AND sa.userId = igsm.user');
$qb->leftJoin('Entity\PackageQuestionPaperHistory', '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;
尽管子查询依赖于主查询,但我可以单独放置子查询吗?如何在我的子查询中添加限制。
谢谢。
解决方案
github中有一个关于这个的问题。
有人问过同样的问题:
一个可能的快速破解解决方案可能是:
- 在选择语句中使用“ order by”列添加所需的选择列
- 使用MIN 或 MAX聚合结果以获得第一个结果(与 LIMIT 1 具有相同的结果)
此子选择:
$qb->addSelect("(SELECT LOWER(ats.assignee) from Entity\AssessmentTimelineStatus ats, Entity\AssessmentStudentTimeline 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 Entity\AssessmentTimelineStatus ats, Entity\AssessmentStudentTimeline 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 聚合函数只选择最高或最低值,它可以是数字或字符串。
参考
推荐阅读
- sas - 在 SAS University Edition 中使用库
- python - 遗漏的数据没有从 Hansard 中被刮掉
- angular - 尝试使用 djangorestframework-simplejwt 和操作符 rxjs 刷新令牌时出错
- python - pandas dataframe python - 使第一行成为最后一行
- javascript - React 16 组件大小属性 (size="test") 被删除
- javascript - 一旦到达数组中的某个键:值对,有没有办法停止 forEach 循环?
- javascript - 迭代 papa.parse 对象的结果
- mongodb - 在 HTML 表中显示 MongoDB 数据正在添加空白行
- django - 对没有表单的页面使用表单向导
- vue.js - 在子组件中使用手表是不是很糟糕?