Getting an error when trying to groupBy in laravel

Question

I'm trying to create a page that will show the amount of approved posts and group them by created_at column.

When I use the code that I've written I get this error

ErrorException SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'blog.posts.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select * from posts where status = approved group by created_at) (View: /home/workspace/Documents/projects/blog/Modules/Posts/Resources/views/index.blade.php)

Here is my code

$approved = Post::where('status', 'approved')->groupBy('created_at')->get();

dd($approved);

Does this answer your question? How can I solve incompatible with sql_mode=only_full_group_by in laravel eloquent? — Remul
– Remul, Commented Oct 21, 2020 at 9:19

Zafar Ahmed · Accepted Answer · 2020-10-21 10:53:13Z

2

$data = DB::table('posts')
                ->groupBy('created_at')
                ->having('status','approved')
                ->get();

just change where with having in your condition

answered Oct 21, 2020 at 10:53

Zafar Ahmed

3693 silver badges4 bronze badges

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

Comments

joekenpat · Accepted Answer · 2020-10-21 09:20:44Z

0

when using groupby clause always add the column name you are grouping by in the select statement EG:

$approved = Post::select('created_at')
                  ->where('status', 'approved')
                  ->groupBy('created_at')->get();

dd($approved);

answered Oct 21, 2020 at 9:20

joekenpat

3874 silver badges16 bronze badges

3 Comments

Nienna Over a year ago

If I do that, then I only get the created_at and I would like to get all the columns not just the created_at column

joekenpat Over a year ago

@Nienna then add all the columns names in the select statement if you need all it's a must

Nienna Over a year ago

That is what I thought so I typed Post::select('id', 'status,' 'created_at'), but then I got the same error as my question though this time it has 'blog.posts.status instead of 'blog.posts.id

Collectives™ on Stack Overflow

Getting an error when trying to groupBy in laravel

2 Answers 2

Comments

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related