Question 299612 in Laravel Question posted in
The official Laravel Documentation can be found here.

Query in Laravel, get multiple data with GroupBy

I have a table with these columns:

event_time (date)
event_data (json)
player_account char()

I’m trying to get all three fields from my query and groupBy by "player_account" field.

The problem is doing this query:

DB::connection('aa')-table('bb')
->where('event_time', ">", $date)
->select('event_time', 'event_data', 'player_account')
->groupBy('player_account')

The query fails without returning anything.

Ideally I’d like to be able to get all records by player_account and then have the other data in the array so I can loop through the results.

Tags:

2

Answers


  1. 0

    GROUP BY groups rows that have the same values into summary rows, so you cannot return all rows when using group by. Instead, since we want all data, we need to pull all the data and than if you want you can perform grouping via some function in php. In this case the grouping is performed in memory, not in the DB layer.

    DB::connection('aa')->table('bb')
  ->where('event_time', ">", $date)
  ->select('event_time', 'event_data', 'player_account')
  ->get() // pull all the data
  ->groupBy('player_account'); // use groupBy method provided by Laravel's collections

    for more information on the groupBy method you can check https://laravel.com/docs/10.x/collections#method-groupby

    Login or Signup to reply.
  2. 0

    The issue you are facing may be related to the way you are using the groupBy clause. When using groupBy you should include all the selected columns that are not part of an aggregate function. In your case you are selecting event_time, event_data, and player_account, but you are grouping only by player_account. You can try this below.

    $results = DB::connection('aa')
->table('bb')
->where('event_time', '>', $date)
->select('player_account', DB::raw('MAX(event_time) as event_time'), 'event_data')
->groupBy('player_account')
->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search