phpmyadmin Question posted in
The official documentation can be found here.

SQL generated by Laravel doesn't work, but if I run the SQL direct on PhpMyAdmin it works

I was trying to get the name of Users in my system and I use the following code on 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();

When I run the code I got the following error:

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`)

But if I get the SQL code from the error above:

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`

and I run the code on PhpMyAdmin, the code it works, but show me this warning message:

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

How can I fix that on Laravel?

Tags:

2

Answers


  1. 0

    When you group rows with GROUP BY, the database doesn’t know which row should be selected. You have to specifically tell the database which row to be shown, for example, FIRST(name) will select the first row of column name.

    Except the column in GROUP BY, which will be unique.

    So in your query, select * won’t work, you should add select() and specify the field and aggregate function.

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

    users.name is in GROUP BY so it doesn’t need aggregate function, other fields need aggregate function.

    Login or Signup to reply.
  2. 0

    Try this:

    From:

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

    To:

     $course_assessments = CourseAssessment::where('assessment_submissions.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();

    I am assuming assessment_submissions table contains canvas_assignment_id column.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search