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

Laravel – Grouping by with the query builder as if it were a collection

I am working on a Laravel app that needs to do a groupBy on a set of items like groupBy does on a collection however doing this on collection after getting the data will not work as I need to slim down the results with query builder and after that the groupBy won’t do anything useful. I understand that the query builder groupBy and the collection groupBy do different things. Is there a way to do what the groupBy on a collection does with the query builder or SQL?

I’ve been googling various things, playing with queries, and asking Bard but came up with nothing it seems the sql group by does things differently.

Tags:

2

Answers


  1. 0

    The short answer is no.

    SQL can only give back a two dimensional set of results. It may help to think of GROUP BY more as an aggregation than actually grouping.

    With Laravel’s collections the group method takes records that share the value you’re grouping by and organises the collection into a series of sub-collections. It only cares about the data it’s grouping by.

    With SQL it’s more a case of merging a number of records into a single row, it needs to know what to do with the ungrouped values. Let’s say you have a table of addresses:

    city street number
    London Oxford Street 100
    London Oxford Street 101
    London Carnaby Street 200
    London Carnaby Street 201
    Bristol Corn Street 302

    If I group by City, SQL wants to give me back two records (London and Bristol) so I need to either aggregate the remaining columns with something like GROUP_CONCAT() or MAX() or further divide the results by adding them to the group parameters.

    If I group by City and Street then it wants to give me 3 records (London/Oxford Street, London/Carnaby Street and Bristol/Corn Street) so I only need to aggregate the number column.

    The long answer is yes but you’d have to manipulate your grouped data into json objects so you can return a single value for each row’s column.
    See: How to put JSON into a column data if sub-query returns more than 1 row in MySQL

    It sounds like you need to get your filters set up on the query so you can limit your result set before using the collection to group the results

    Login or Signup to reply.
  2. 0

    The original question doesn’t cover the full context, but it sounds like your goal is to select every 50th record for each set_id in a table. This is achievable in SQL directly, but it’s not exactly straightforward.

    For consistent results, you’ll need to add an ORDER BY. This is important since generally MySQL will return things sorted by primary key, but it’s not a guarantee.

    The query you’re trying to come up with looks something like this:

    SELECT 
  t.sensor_id, 
  t.value, 
  t.set_id 
FROM (
  SELECT 
    sensor_id, 
    value, 
    set_id, 
    ROW_NUMBER() OVER (PARTITION BY set_id ORDER BY some_column_here) as rn
  FROM 
    your_table
) as t 
WHERE t.rn % 50 = 0; # Assuming you literally meant every 50th row. Otherwise just use "= 50" instead.

    Which should convert to a query something like this in Laravel:

    $results = DB::table('your_table')
    ->select('sensor_id', 'value', 'set_id')
    ->selectRaw('ROW_NUMBER() OVER (PARTITION BY set_id ORDER BY some_ordering_column) as rn')
    ->fromSub(function ($query) {
        $query->from('your_table')->selectRaw('sensor_id, value, set_id');
    }, 't')
    // Replace with `where->('rn', 50)` if you just want the 50th record, not every 50th.
    ->whereRaw('rn % 50 = 0')
    ->get();
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search