Executing raw query in Laravel 5.3

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?

Solution

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

Get Ted James’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

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

Answered By — Frondor

Answer Checked By — David Marino (FixIt Volunteer)

This Answer collected from stackoverflow, is licensed under cc by-sa 2.5 , cc by-sa 3.0 and cc by-sa 4.0