Using groupBy() with orderBy() and limit()

Question

I have a DB table that contains records for over 1000 assets and each asset can have 1000's of entries.

eg:

ID         ASSET_ID  MANY_COLUMNS_OF_DATA  CREATED_AT
1          345       Stuff                 2018-04-01....
....
200        946       Stuff                 .....
...
3000       345       Stuff                 2018-05-01....
....
1000000    925       Stuff                 2018-05-01....

In one call I want to be able to:

1. Specify the ASSET_ID's I require. whereIn() I believe.
2. Group the results by ASSET_ID
3. Only get those records at or before a certain date
4. Get them in reverse date order. I could use orderBy('id', 'desc) I believe.
5. Take a specific number of records for each ASSET_ID

I could be needing any number of assets to be returned, from 1 to n.

I have played around with various approaches but something is wrong as I get only one record returned.

For example:

        USING THE DB FACADE
        $data = DB::table( 'table' )
            ->whereIn( 'asset_id', $assetIDs )
            ->groupBy( 'asset_id' )
            ->having( 'created_at', '<=', $this->dateINeed )
            ->limit( $numberOfRowsINeed )
            ->get();

        USING ELOQUENT
        $data = $modelToRead->whereIn( 'asset_id', $assetIDs )
            ->groupBy( 'asset_id' )
            ->where( 'created_at', '<=', $this->dateINeed )
            ->orderBy( 'id', 'desc' )
            ->take( $numberOfRowsINeed )
            ->get();

So what I would like returned is an array of data with the records for each asset listed separately, eg:

$data = [
   '345' => [
       .....
       .....
    ],
   '234' => [
       .....
       .....
    ],
   '123' => [
       .....
       .....
    ],
   'etc' => [
       .....
       .....
    ],
]

Any help would be most appreciated.

Thanks.

Answer 1

SQL grouping is used for aggregation of data, not for organization of output.

Remove the Query Builder groupBy method call (SQL), get all the rows you need, and return a collection.

$collection = $model->...->get();

Then use the collection method groupBy to organize all the collections by asset id.

$grouped = $collection->groupBy('asset_id');