php - How to apply a where condition on average function results without second select?

730

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
}
906

Answer

Solution:

You cannot apply aWHERE condition to an aggregate function. Databases evaluate theWHERE clause first, and then aggregate later.

Instead, you can use aHAVING clause for this (it is evaluated after theGROUP BY clause).

So you would need to change this:

WHERE 
    engaged = 1 AND `questions`.`question_type_id` = '6S'
GROUP BY
    `employees`.`id`

To:

WHERE 
    `questions`.`question_type_id` = '6S'
GROUP BY
    `employees`.`id`
HAVING
    engaged = 1

In Lavarel, this should look like:

$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');

People are also looking for solutions to the problem: javascript - Why isn't session id persisting in same domain

Source

Didn't find the answer?

Our community is visited by hundreds of web development professionals every day. Ask your question and get a quick answer for free.

Ask a Question

Write quick answer

Do you know the answer to this question? Write a quick response to it. With your help, we will make our community stronger.

Similar questions

Find the answer in similar questions on our website.