Executing raw query in Laravel 5.3

Question

I am trying to execute some raw queries in Laravel 5.3. the queries are simple but I'm getting errors.

table name: users
columns: id|name|email|phone|created_at|updated_at

my query:

SELECT created_at AS member_since, count(*) as row_count
FROM users
GROUP by MONTH(created_at);

This raw query works fine when I execute this on phpMyAdmin. But when I execute this using Laravel's database query builder I get an error.

SQLSTATE[42000]: Syntax error or access violation: 1055 
'query.users.created_at' isn't in GROUP BY
SQLSTATE[42000]: Syntax error or access violation: 1055 
    'query.users.created_at' isn't in GROUP BY (SQL: select created_at AS 
    member_since from `users` group by MONTH(created_at))

Here is my controller:

$users = DB::table('users')
            ->select(DB::raw('created_at AS member_since', 'count(*) AS row_count'))
            ->groupBy(DB::raw('MONTH(created_at)'))
            ->get();

return response()->json($users);

Please correct me if I'm wrong. Are there any better ways to execute raw queries?

Answer 1

First, you need to understand the error you're getting. This question is all about it https://stackoverflow.com/a/38551525/2474872

Now, with that in mind you have two choices

• Disable only_full_group_bysetting.

• Follow the indications provided by the error message:

    $users = DB::table('users')
      ->select(DB::raw('created_at AS member_since, count(*) AS row_count'))
      ->groupBy(DB::raw('MONTH(created_at)'), 'created_at')
      ->get();
  

groupBy(DB::raw('MONTH(created_at)'), 'created_at') can also be groupBy(DB::raw('MONTH(created_at), created_at')