Laravel生成的SQL不起作用，但如果我在PhpMyAdmin上直接运行SQL，它就起作用了

Question

我试图获取系统中用户的名称，并且在PHP Laravel上使用以下代码：

$course_assessments = CourseAssessment::where('canvas_assignment_id', '=', $canvas_assignment_id)
        ->join('assessment_submissions', 'assessment_submissions.courses_assessments_id', '=', 'course_assessments.id')
        ->join('users', 'users.id', '=', 'assessment_submissions.evaluated_user')
        ->groupby('users.name')
        ->get();

当我运行代码时，我得到以下错误：

SQLSTATE[42000]: Syntax error or access violation: 1055 'laravel.course_assessments.id' isn't in GROUP BY (SQL: select * from `course_assessments` inner join `assessment_submissions` on `assessment_submissions`.`courses_assessments_id` = `course_assessments`.`id` inner join `users` on `users`.`id` = `assessment_submissions`.`evaluated_user` where `canvas_assignment_id` = 248302 group by `assessment_submissions`.`evaluated_user`)

但如果我从上面的错误中得到SQL代码：

select * from `course_assessments` inner join `assessment_submissions` on `assessment_submissions`.`courses_assessments_id` = `course_assessments`.`id` inner join `users` on `users`.`id` = `assessment_submissions`.`evaluated_user` where `canvas_assignment_id` = 248302 group by `assessment_submissions`.`evaluated_user`

并且我在PhpMyAdmin上运行代码,它工作的代码,但向我显示以下警告消息：

Current selection does not contain a unique column. Grid edit, checkbox, Edit, Copy and Delete features are not available. 

我怎么能在拉拉维尔身上修好呢？

Answer 1

使用group BY对行进行分组时，数据库不知道应选择哪一行。您必须明确地告诉数据库要显示哪一行，例如，first(name)将选择列名的第一行。

GROUP BY中的列除外，该列将是唯一的。

因此在查询中，select*将不起作用，您应该添加select()并指定字段和聚合函数。

        ->select('users.name', DB::raw('FIRST(course_assessments.field_name)'))

users.name在GROUP BY中，所以不需要聚合函数，其他字段需要聚合函数。