How to issue query in Laravel?

Question

I need create query in Laravel for chart.

I need create this json file:

For example: (30 days ago)

[{
            "day": "23",
            "count": 20
        }, {
            "day": "22",
            "visits": 10
        }, {
            "day": "21",
            "visits": 12
        }, {
            "day": "20",
            "visits": 30
        }, {
            "day": "19",
            "visits": 20
        }, {
            "day": "18",
            "visits": 10
        }, 
         .
         .
         .
         .
        }, {
            "day": "22",
            "visits": 10
        }]

My code in Laravel is:

 $date = Carbon::today()->subDays(30);
 $ordersChart = Order::selectRaw('dayname(created_at) day, count(*) count')
        ->where('created_at', '>=', $date)
        ->groupBy('day')
        ->latest()
        ->get();

But after return $ordersChart show this error:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'laravel.orders.created_at' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (SQL: select dayname(created_at) day, count(*) count from `orders` where `created_at` >= 2018-03-20 00:00:00 group by `day` order by `created_at` desc)

How to issue this problem?

Turn strict mode to false in your config/database.php file's mysql block and then try your query — Khan Shahrukh
– Khan Shahrukh, Commented Apr 19, 2018 at 11:41
latest() means orderBy('updated_at', 'desc') you might have multiple updated at per group which makes orderBy make no real sense — apokryfos
– apokryfos, Commented Apr 19, 2018 at 13:24

Jonas Staudenmeir · Accepted Answer · 2018-04-19 12:07:49Z

1

You have to use ->latest('day').

answered Apr 19, 2018 at 12:07

Jonas Staudenmeir

26k7 gold badges70 silver badges114 bronze badges

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

Comments

mySun · Accepted Answer · 2018-04-19 13:25:38Z

0

You have to use:

1- dayname(created_at) => day(created_at)

2- latest() => latest('day')

$ordersChart = Order::selectRaw('day(created_at) day, count(*) count')
    ->where('created_at', '>=', $date)
    ->groupBy('day')
    ->latest('day')
    ->get();

answered Apr 19, 2018 at 13:25

mySun

1,7067 gold badges34 silver badges58 bronze badges

Collectives™ on Stack Overflow

How to issue query in Laravel?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related