php - How to apply a where condition on average function results without second select?
I have a Laravel 6 application. I want to use eloquent to collect only those employees that have an engaged score equal to true.
The question is how can I apply awhere
clause on theavg
function results without a second select in Eloqunt? For example something like this:
SELECT
`employees`.*,
AVG(responses.text_answer) > 4.6 AS engaged
FROM
`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`
But the problem isengaged
in the WHERE condition here is not recognized and MySQL shows:
Unknown column 'engaged' in 'where clause'
A part of my eloquent statement is like this:
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');
});
}
And the where clause applied like this:
public function filterEngaged(EloquentBuilder $query, $method, $clauseOperator, $value, $column, $table)
{
call_user_func([$query, $method], $column, $value);
// $method = 'where', $column = 'engaged' , $value = 1
}
Answer
Solution:
You cannot apply a
WHERE
condition to an aggregate function. Databases evaluate theWHERE
clause first, and then aggregate later.Instead, you can use a
HAVING
clause for this (it is evaluated after theGROUP BY
clause).So you would need to change this:
To:
In Lavarel, this should look like: