Laravel select columns and then group column value by date

Question

I'm trying to get what should seem like a simple SQL query to work in my Laravel project, I've got a front-end web application that allows a user to pull data from various pre-defined tables and a selectable list of columns.

This works fine if I don't attempt to select and groupBy together, equally, I need someway of grouping baed on whether the user wants to group data by a day, or a month for instance.

My POST request looks like, where each array item inside the parent array is a table:

[
  [
    table: 'my_table',
    columns: ['event_category', 'event_action', 'event_count', 'created_at'] ...
    filterBy: [
      ['event_category', 'my category'],
      ['event_action', 'some action']
    ],
    orderBy: {
      field: 'created_at',
      direction: 'desc'
    }
  ]
]

Each row in my_table contains an event_count column, which contains a number, so if there's 5 rows for a particular day with different event_count numbers, I need to add up all of those event_count entries and group them by that day

Here's my function and query:

public function findDataFromSources(Request $request)
{

    $request_data = $request->all();
    $realData = [
      'size' => 0,
      'results' => [],
      'filtered' => []
    ];

    foreach ($request_data as $key => $findable) {
      // NOTE: this works for retrieving data that isn't grouped
      // $res = DB::table($findable['table'])
      //          ->select($findable['columns'])
      //          ->where($findable['filterBy'])
      //          ->orderBy($findable['orderBy']['field'], $findable['orderBy']['direction'])
      //          ->take(20)
      //          ->get();

      // TODO: this isn't grouping for some reason...
      $res = DB::table($findable['table'])
              ->select($findable['columns'], DB::raw('sum(event_count) as total_events'))
              ->groupBy('created_at')
              ->orderBy($findable['orderBy']['field'], $findable['orderBy']['direction'])
              ->get();

      $realData['size'] += count($res);
      array_push($realData['results'], $res);
    }

    $data = [
      'success' => true,
      'message' => 'Your chosen data sources and fields',
      'sources' => $realData
    ];

}

What am I missing? The error I'm getting is:

SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column

try to add created_at in the selected method like this :- ->select('created_at', $findable['columns'], DB::raw('sum(event_count) as total_events')) because when you try to group the row with specific column , you should add it in the select method . — Mohamed Ahmed, Mar 04 '21 at 09:51
This might help you https://stackoverflow.com/a/66356459/3341543 — Robbin Benard, Mar 04 '21 at 10:31
Not entirely sure whether that solution @RobbinBenard is working for me, maybe I'm misunderstanidng the `selectRaw` part of it, my `selectRaw` is now: `->selectRaw($findable['columns'], 'ANY_VALUE(event_count)', 'COUNT(*) AS total_events')` which gives me an error about it needing to be an array — Ryan H, Mar 04 '21 at 10:57

Anurat Chapanond · Answer 1 · 2021-03-10T04:30:06.517

0

->select($findable['columns']

You cannot SELECT fields/columns that are not in GROUP BY without aggregate function and your $findable['columns'] probably contain fields that are not in GROUP BY.

You can use ANY_VALUE() like @RobbinBenard commented for example

->select(DB::raw('ANY_VALUE(' .$findable['columns'][0] .')'))

You can loop through the columns and use addSelect() to add those columns.

  $query = DB::table($findable['table'])
          ->select(DB::raw('sum(event_count) as total_events'));

  foreach($findable['columns'] as $column) {
          $query->addSelect(DB::raw('ANY_VALUE(' .$column .')'));
  }

  $res = $query->groupBy('created_at')
         ->orderBy($findable['orderBy']['field'], $findable['orderBy']['direction'])
         ->get();

edited Mar 10 '21 at 04:30

answered Mar 04 '21 at 11:03

Anurat Chapanond

2,837
3
18
31

Brilliant, but in your example, you're selecting the first from the array, I need to select all of them and then group by a specific column – Ryan H Mar 04 '21 at 11:17
@RyanH you can loop through your columns and add to the query, see the updated code – Anurat Chapanond Mar 10 '21 at 04:30

Laravel select columns and then group column value by date

1 Answers1