php - SQL query in Laravel DB::raw() to order column and get first result

one text

Solution:

I don't think FIRST is a function at all, hence the error. If we break your problems into two you want:

1. One row for each day with the event_count column present. As per your comment above the column increments and you want the last value, thus you want the max value per day.
2. Sum the values of the event_count column, grouping by the month and year

1:

A select statement for this would look like

select date(created_at), max(event_count) from google_analytics group by date(created_at) where ...

Using the Eloquent builder it would look something like this:

$query = GoogleAnalytics::select(
            DB::raw('date(created_at) as created_at'),
            DB::raw('max(event_count) as sessions')
        )
        ->where('event_category', 'Category')
        ->where('event_action', 'Action')
        ->whereDate('period_from', '>=', '2021-06-08')
        ->whereDate('period_to', '<=', '2021-06-08')
        ->groupByRaw('date(created_at)');

Note:

• your where clause only looks at one date (2021-06-08)
• this query will not return complete GoogleAnalytics models

2:

We can use the above $query to group the results per month:

$result = DB::table($query, 'per_day')->select(
            DB::raw('date_format(created_at, "%Y-%m") as created_at'),
            DB::raw('sum(sessions) as sessions')
        )
        ->groupByRaw('date_format(created_at, "%Y-%m")')
        ->orderByRaw('date_format(created_at, "%Y-%m") desc')
        ->get();