我有一個Laravel 6應用程序。我想用雄辯的方式只收集那些敬業度得分等于true的員工。問題是，如何在函數結果上應用子句而不在 Eloqunt 中進行第二次選擇？例如，像這樣：whereavgSELECT `employees`.*, AVG(responses.text_answer) > 4.6 AS engagedFROM `employees`INNER JOIN `responses` AS `responses`ON `responses`.`employee_id` = `employees`.`id`INNER JOIN `survey_questions` AS `surveyQuestions`ON `surveyQuestions`.`id` = `responses`.`survey_question_id`INNER JOIN `questions` AS `questions`ON `questions`.`id` = `surveyQuestions`.`question_id`WHERE engaged = 1 AND `questions`.`question_type_id` = '6S'GROUP BY `employees`.`id`但問題是這里的WHERE條件無法識別，MySQL顯示：engaged“where 子句”中的“已參與”列未知我雄辯的一部分陳述是這樣的：public function engaged() { return $this->relatedToMany(QuestionType::class, function(Builder $query){ if(!$this->joined($query, 'responses')){ $query->join( 'responses AS responses', 'responses.employee_id', '=', 'employees.id', 'inner' ); } if(!$this->joined($query, 'survey_questions')){ $query->join( 'survey_questions AS surveyQuestions', 'surveyQuestions.id', '=', 'responses.survey_question_id', 'inner' ); } if(!$this->joined($query, 'questions')){ $query->join( 'questions AS questions', 'questions.id', '=', 'surveyQuestions.question_id', 'inner' ); } $query->where('questions.question_type_id', '6S'); $query->select('employees.*', \DB::raw('AVG(`responses`.`text_answer`) >= 4.6 AS `engaged`')); $query->groupBy('employees.id'); }); }
1 回答
qq_花開花謝_0
TA貢獻1835條經驗 獲得超7個贊
不能將條件應用于聚合函數。數據庫首先計算子句，然后聚合。WHEREWHERE
相反，您可以對此使用子句（在子句之后計算）。HAVINGGROUP BY
因此，您需要更改以下內容：
WHERE
engaged = 1 AND `questions`.`question_type_id` = '6S'
GROUP BY
`employees`.`id`
自：
WHERE
`questions`.`question_type_id` = '6S'
GROUP BY
`employees`.`id`
HAVING
engaged = 1
在Lavare中，這應該看起來像這樣：
$query->where('questions.question_type_id', '6S');
$query->select('employees.*', \DB::raw('AVG(`responses`.`text_answer`) >= 4.6 AS `engaged`'));
$query->groupBy('employees.id');
$query->havingRaw('engaged = 1');
- 1 回答
- 0 關注
- 78 瀏覽
添加回答
舉報
0/150
提交
取消