Raw Mysql query working on phpMyAdmin returning error in laravel

Question

I have this Eloquent query:

return DB::table('users')
        ->leftJoin('bet', function($join) use ($competition_id){
          $join->on('users.id', '=', 'bet.user_id');
          $join->on('bet.competition_id','=', DB::raw("$competition_id"));
        })
        ->select('users.*', DB::raw('SUM(points) as score'))->get();

it returns me an error:

SQLSTATE[42000]: Syntax error or access violation: 1140 Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause (SQL: select users.*, SUM(points) as score from users left join bet on users.id = bet.user_id and bet.competition_id = 1)

if I just copy-paste the generated SQL (in bold) in PhpMyAdmin, it works perfectly...

Could you please help me solving this issue? because it makes no sense to me...

Thank you

SOLUTION

The problem was just the missing group by:

return DB::table('users')
    ->leftJoin('bet', function($join) use ($competition_id){
      $join->on('users.id', '=', 'bet.user_id');
      $join->on('bet.competition_id','=', DB::raw("$competition_id"));
    })
    ->select('users.*', DB::raw('SUM(points) as score'))->groupBy('users.id')->get();

SOLUTION #2

Depending on the version or configuration of MySQL, the solution above is not working and I had to groupBy all the fields as advised by Eric:

return DB::table('users')
    ->leftJoin('bet', function ($join) use ($competition_id) {
        $join->on('users.id', '=', 'bet.user_id');
        $join->on('bet.competition_id', '=', DB::raw("$competition_id"));
    })
    ->select('users.*', DB::raw('SUM(points) as score'))
    ->groupBy('users.id')
    ->groupBy('users.name')
    ->groupBy('users.firstName')
    ->groupBy('users.lastName')
    ->groupBy('users.email')
    ->groupBy('users.admin')
    ->groupBy('users.password')
    ->groupBy('users.remember_token')
    ->groupBy('users.created_at')
    ->groupBy('users.updated_at')->get();
}

Your laravel is probably using strict mode, while the servers default isn't. You probably need to add ->groupBy('users.id). — Paul Spiegel
– Paul Spiegel, Commented Mar 8, 2018 at 21:06
You have SUM(), but I don't see any GROUP BY in your statement. — Eric
– Eric, Commented Mar 8, 2018 at 21:13
Put all non-aggregated columns in GROUP BY. Your query won't even run in all dbms, except for maybe MySQL. — Eric
– Eric, Commented Mar 8, 2018 at 21:14

abr · Accepted Answer · 2018-03-09 09:36:03Z

2

I have many questions but 4. is the cause of it

Why aren't you using the ORM? You should take advantage of the framework whenever possible
Why DB::raw on $competition_id? it's just a value you're inserting into the query
You can use selectRaw on your final select to avoid another DB::raw
It is most likely the lack of a groupBy (your sum is at fault). You can do this by placing a group by or disabling strict mode on MySql

Try this:

return DB::table('users')
        ->leftJoin('bet', function($join) use ($competition_id){
          $join->on('users.id', '=', 'bet.user_id');
          $join->on('bet.competition_id','=', DB::raw("$competition_id"));
        })
        ->select('users.*', DB::raw('SUM(points) as score'))->groupBy('users.id')->get();

edited Mar 9, 2018 at 9:36

answered Mar 8, 2018 at 21:09

abr

2,0941 gold badge22 silver badges39 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

theplayer777 Over a year ago

I use $competition_id in a raw request otherwise it seems to be considered as a column: (unknown column '1'). Your query is almost good, I just had to change a bit the select part to make it work (see my updated post). Thanks!

abr Over a year ago

Glad you got it to work! If you disable strict mode, you won't need groupBy (although you should). This is because: If you are going to sum a column, mysql needs to know what is it based on :) Feel free to classify this answer as a solution or provide the solution for future users

theplayer777 Over a year ago

What is funny with this request is that depending on which computer i'm developing my app, it is still not working as is. I think it's related to the version and/or configuration of MySQL. See my updated answer to have the other solution. Thanks again!

Collectives™ on Stack Overflow

Raw Mysql query working on phpMyAdmin returning error in laravel

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related