如何在没有第二选择的情况下在平均函数结果上应用where条件? [英] How to apply a where condition on average function results without second select?

问题描述

我有一个Laravel 6应用程序.我只想雄辩地收集那些参与度等于真值的员工.

I have a Laravel 6 application. I want to use eloquent to collect only those employees that have an engaged score equal to true.

问题是，如何在 avg 函数结果上应用 where 子句，而无需在Eloqunt中进行第二次选择?例如这样的东西:

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`

但是问题是在WHERE条件下 enged ，此处无法识别并且MySQL显示:

But the problem is engaged in the WHERE condition here is not recognized and MySQL shows:

"where子句"中的未知列"engaged"

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

where子句的用法如下:

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
}

推荐答案

您不能将 WHERE 条件应用于聚合函数.数据库首先评估 WHERE 子句，然后再进行汇总.

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

相反，您可以为此使用 HAVING 子句(在 GROUP BY 子句的之后进行评估.)

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`

收件人:

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

在Lavarel中，外观应为:

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

这篇关于如何在没有第二选择的情况下在平均函数结果上应用where条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！