0

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);
Nienna
  • 393
  • 2
  • 11
  • Does this answer your question? [How can I solve incompatible with sql\_mode=only\_full\_group\_by in laravel eloquent?](https://stackoverflow.com/questions/43776758/how-can-i-solve-incompatible-with-sql-mode-only-full-group-by-in-laravel-eloquen) – Remul Oct 21 '20 at 09:19
  • Wouldn't that allow for some security risks? – Nienna Oct 21 '20 at 09:35

2 Answers2

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

just change where with having in your condition

Zafar Ahmed
  • 329
  • 3
  • 4
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);
joekenpat
  • 387
  • 3
  • 16
  • 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 – Nienna Oct 21 '20 at 09:36
  • @Nienna then add all the columns names in the select statement if you need all it's a must – joekenpat Oct 21 '20 at 09:39
  • 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` – Nienna Oct 21 '20 at 09:48