CODEWITHSUNDEEP
phplaravel
Vishal Srivastava
Vishal Srivastava

Reputation: 592

How to Get All Records and Group Them by create_at (Date Only)

I have a table named shopping_apps, and that table has different entries on different dates. I want to have all records but group all the records on a date basis (not the time part). Suppose I have 20 entries on date1, 12 entries on date2. I want to have only two values extracted from the database: date1 and date2. The controller is below, and Shopping_app is my model name.

<?php

// Use substr() to retrieve date part 10 chars in length.
$shoppingApp = Shopping_app::orderBy('created_at', 'DESC')
    ->groupBy(substr('created_at', 0, 10))
    ->get();

ERROR:

SQLSTATE[42000]: Syntax error or access violation: 1055 'laravelpro4.shopping_apps.id' isn't in GROUP BY (SQL: select * from shopping_apps group by created_at order by created_at desc)

Upvotes: 1

Views: 240

Answers (2)

Vishal Srivastava
Vishal Srivastava

Reputation: 592

So with the help of @manish I get the correct answer (what I was trying to get)

Error solved by

Adding false

In config\database.php --> "mysql" array Set 'strict' => false to disable all.

Final code :

$shoppingApp = Shopping_app::where('created_at', '>=', \Carbon\Carbon::now()->subMonth())
                    ->groupBy(DB::raw('Date(created_at)'))
                    ->orderBy('created_at', 'DESC')->get();

Upvotes: 1

Manish
Manish

Reputation: 138

$shoppingApp = Shopping_app::where('created_at', '>=', \Carbon\Carbon::now->subMonth())
                    ->groupBy(DB::raw('Date(created_at)'))
                    ->orderBy('created_at', 'DESC')->get();

try this maybe it will help you

Upvotes: 1

Related Questions