Laravel Query Builder无法正常工作，显示错误

I have the following query used in Laravel Framework

我在Laravel Framework中使用了以下查询

$student_attendance_search_result = DB::table('tbl_student_attendance')
        ->join('tbl_student_admission', 'tbl_student_attendance.student_id', '=', 'tbl_student_admission.student_id')
        ->select(DB::raw('tbl_student_attendance.*,tbl_student_admission.student_id,tbl_student_admission.student_full_name_english,tbl_student_admission.class,tbl_student_admission.section,tbl_student_admission.roll_no, count(case when attendance_status ="Absent"  then 1 end) as total_absent,count(case when attendance_status ="Present" then 1 end) as total_present,count(case when attendance_status ="Leave" then 1 end) as total_leave,count(distinct date) as total_class'))
        ->where('tbl_student_attendance.academic_year', $academic_year)
        ->where('tbl_student_admission.class', $classs)
        ->where('tbl_student_admission.section', $section)
        ->where('tbl_student_attendance.month', $month)
        ->groupBy('tbl_student_admission.student_id')
        ->get();

It is throwing the following exception when i am trying to run it

当我试图运行它时,它抛出以下异常

SQLSTATE[42000]: Syntax error or access violation: 1055 'db_smsfinal1user.tbl_student_attendance.student_attendance_id' isn't in GROUP BY (SQL: select tbl_student_attendance.*, tbl_student_admission.student_id, tbl_student_admission.student_full_name_english, tbl_student_admission.class, tbl_student_admission.section, tbl_student_admission.roll_no, count(case when attendance_status ="Absent" then 1 end) as total_absent,count(case when attendance_status ="Present" then 1 end) as total_present,count(case when attendance_status ="Leave" then 1 end) as total_leave,count(distinct date) as total_class from tbl_student_attendance inner join tbl_student_admission on tbl_student_attendance.student_id = tbl_student_admission.student_id where tbl_student_attendance.academic_year = 2018 and tbl_student_admission.class = 2 and tbl_student_admission.section = None and tbl_student_attendance.month = September group by tbl_student_admission.student_id)

SQLSTATE [42000]:语法错误或访问冲突:1055'db_smsfinal1user.tbl_student_attendance.student_attendance_id'不在GROUP BY中(SQL:select tbl_student_attendance。*,tbl_student_admission.student_id,tbl_student_admission.student_full_name_english,tbl_student_admission.class,tbl_student_admission.csection,tbl_student_admission .roll_no,count(attendance_status =“Absent”然后1结束时的情况)作为total_absent,count(attendance_status =“Present”然后1结束时的情况)作为total_present,count(attendance_status =“Leave”然后1结束时的情况)as total_leave ,计数(不同日期)作为total_class从tbl_student_attendance内上tbl_student_attendance.student_id = tbl_student_admission.student_id加入tbl_student_admission其中tbl_student_attendance.academic_year = 2018和tbl_student_admission.class = 2和tbl_student_admission.section =无和tbl_student_attendance.month = 9月tbl_student_admission.student_id组)

Any idea what i am doing wrong?

知道我做错了什么吗?

1 个解决方案

#1

use DB::raw as select arguments

使用DB :: raw作为选择参数

DB::select( DB::raw('student_id,count(case when attendance_status ='Absent' then 1 end) as absent_count,count(case when attendance_status ='Present' then 1 end) as present_count,count(case when attendance_status ='Leave' then 1 end) as leave_count,count(distinct date) as Tot_count') )->from(..)->groupBy(...);

DB :: select(DB :: raw('attend_id,count(出勤情况='缺席',然后1结束时的情况)为absent_count,count(出勤情况=出现'然后1结束时的情况)为present_count,count(出勤情况时的情况) ='离开'然后1结束)as leave_count,count(distinct date)as Tot_count')) - > from(..) - > groupBy(...);