John Davidson

Message:

I'm trying to order a specific column in my DB::raw() select statement, I need to order the items for each day and get the first one for each day, so I'd end up with 5 results for 5 months but my error is

Illuminate\Database\QueryException with message 'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax

What am I missing?

GoogleAnalytics::where('event_category', 'Category')

                ->where('event_action', 'Action')

                ->whereDate('period_from', '>=', '2021-06-08')

                ->whereDate('period_to', '<=', '2021-06-08')

                ->select(DB::raw('DATE_FORMAT(created_at, "%Y-%m") as created_at'), DB::raw('FIRST(event_count ORDER BY created_at DESC ) as sessions'))

                ->groupByRaw('DATE_FORMAT(created_at, "%Y-%m")')

                ->orderBy('created_at', 'desc')

                ->get();

Some more context here, each day might have 100 entries, so there could be 3,000 entries per month, I need to get just a single entry in each day so I end up with 30 entries, and then add up those 30 result to produce a single result for that one month, so I'd end up with 5 items in my returned collection, but right now I can't seem to get DB::raw('FIRST(event_count ORDER BY created_at DESC ) as sessions') to grab the column, order it and just get the first result.

---

My Answer:
To order a specific column in your DB::raw() select statement and get the first result for each day, you can use the following SQL query in Laravel:

php
$results = GoogleAnalytics::select(DB::raw('DATE(created_at) as date, MAX(column_name) as max_column'))
->where('event_category', 'Category')
->groupBy('date')
->orderBy('date')
->get();


In this query, replace `column_name` with the name of the column you want to order by. This query will group the results by date, order them by date, and get the maximum value of the specified column for each day.

Make sure to adjust the column names and table names according to your database schema.