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 a where clause on the avg 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 is engaged 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
}

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

You cannot apply a WHERE condition to an aggregate function. Databases evaluate the WHERE clause first, and then aggregate later.

Instead, you can use a HAVING clause for this (it is evaluated after the GROUP 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');


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x