Laravel eloquent get the latest rows of grouped rows

Question

Using Eloquent, trying to find a way to get the latest rows of every row grouped by: exchange, base, quote

Data

exchange    base    quote   price   value   created_at

bittrex     BTC     USD     10000   10000   2018-01-05
bittrex     BTC     USD     9000    9000    2018-01-01
poloniex    BTC     USD     10001   10001   2018-01-05
poloniex    BTC     USD     9000    9000    2018-01-01
binance     BTC     USD     10002   10002   2018-01-05
binance     BTC     USD     9000    9000    2018-01-01
binance     ETH     USD     800     800     2018-01-05
binance     ETH     USD     700     700     2018-01-01

Result:

bittrex     BTC     USD     10000   10000   2018-01-05
poloniex    BTC     USD     10001   10001   2018-01-05
binance     BTC     USD     10002   10002   2018-01-05
binance     ETH     USD     800     800     2018-01-05

UPDATE

I went with @Cryode solution, raw SQL instead of Eloquent (if anyone can come up with one Eloquent query to replicate the results of the query below, feel free to post).

I've also changed the structure of the table to add id (increments) as the primary key. I also added the following index $table->index(['exchange', 'base', 'quote', 'created_at']);

Here is the solution:

$currencies  = DB::select('SELECT *
                             FROM (
                                    SELECT DISTINCT exchange, base, quote
                                      FROM tickers
                                  ) AS t1
                             JOIN tickers
                               ON tickers.id =
                                 (
                                    SELECT id
                                      FROM tickers AS t2
                                     WHERE t2.exchange  = t1.exchange
                                       AND t2.base      = t1.base
                                       AND t2.quote     = t1.quote
                                     ORDER BY created_at DESC
                                     LIMIT 1
                                 )
                         ');

Thanks

Can you try this ? `Table::orderBy('created_at','asc')->groupBy('exchange')->get()` — jerome, Feb 24 '18 at 02:31
Hmm, tried this: `Ticker::orderBy('created_at','asc')->groupBy('exchange', 'base_currency', 'quote_currency')->get()` but not sure how this would retrieve only the latest row of each "group" — user3489502, Feb 24 '18 at 02:39
something like Ticker::distinct()->orderBy('created_at', 'asc')->groupBy('exchange')->get(['exchange']); this would get distinct exchanges...i feel this is near to solution. — syam, Feb 24 '18 at 03:44

score 6 · Accepted Answer · answered Feb 25 '18 at 06:50

Let's first determine what this SQL query would actually look like.

This DBA answer provides some great insight into the "greatest-n-per-group" problem, as well as PostgreSQL and MySQL examples. Inspired by this answer, here's what I've come up with for your single table (assuming MySQL as your DB):

SELECT ticker.*
  FROM (
    SELECT DISTINCT exchange, base, quote
      FROM ticker
  ) AS exchanges
  JOIN ticker
    ON ticker.id = 
       (
         SELECT id
           FROM ticker
          WHERE ticker.exchange = exchanges.exchange
            AND ticker.base = exchanges.base
            AND ticker.quote = exchanges.quote
       ORDER BY created_at DESC
          LIMIT 1
       );

Oh dear. Getting that into Laravel-speak doesn't look easy.

Personally, I wouldn't even try. Complicated SQL queries are just that because they take advantage of your database to do reporting, data gathering, etc. Trying to shove this into a query builder is tedious and likely comes with little to no benefit.

That said, if you'd like to achieve the same result in a simple way using Laravel's query builder and Eloquent, here's an option:

// Get the unique sets of tickers we need to fetch.
$exchanges = DB::table('ticker')
    ->select('exchange, base, quote')
    ->distinct()
    ->get();

// Create an empty collection to hold our latest ticker rows,
// because we're going to fetch them one at a time. This could be
// an array or however you want to hold the results.
$latest = new Collection();

foreach ($exchanges as $exchange) {
    $latest->add(
        // Find each group's latest row using Eloquent + standard modifiers.
        Ticker::where([
                'exchange' => $exchange->exchange,
                'base' => $exchange->base,
                'quote' => $exchange->quote,
            ])
            ->latest()
            ->first()
    );
}

Pros: You can use the query builder and Eloquent abstractions; allows you to maintain your Ticker model which may have additional logic needed during the request.

Cons: Requires multiple queries.

Another option could be to use a MySQL View that encapsulates the complicated query, and create a separate Eloquent model which would fetch from that view. That way, your app code could be as simple as TickerLatest::all().

Thanks for your help, I'm trying to test the MySQL above but since the first Select doesn't retrieve `id`, but only `exchange`, `base_currency`, `quote_currency`, I can't join on `id`. The `foreach` option is nice, but that would hit the db hundreds of times on each call. Will look into all of that. I guess using Eloquent will be out of the question, and I should use a raw query — user3489502, Feb 25 '18 at 15:37
oups meant `base` and `quote`, not `base_currency` and `quote_currency` — user3489502, Feb 25 '18 at 15:45
You should have one not for performance, but to make additional queries (like this one) possible / easier. If you want better performance, you could normalize the data that's repeated, add indexes (see the SO Q&A I linked for index recommendations), etc. A primary key also would allow MySQL to be scaled in the future. — Aken Roberts, Feb 25 '18 at 20:15

Dexter Bengil · Answer 2 · 2019-12-17T13:00:49.537

0

You can fetch the latest rows first then group the collection later.

$items = Ticker::latest()->get();
// exchange, base, quote
$groupedByExchange = $items->groupBy('exchange');
$groupedByBase = $items->groupBy('base');
$groupedByQoute = $items->groupBy('qoute');

UPDATE: You can get the single item by each group by simple adding ->first() after the groupBy() function.

$latestByExchange= Ticker::latest()->groupBy('exchange')->first(); // and so on

edited Dec 17 '19 at 13:00

answered Feb 24 '18 at 07:23

Dexter Bengil

5,995
6
35
54

Thanks latest will order by desc but is possible to group exchange, base, quote, and have one latest values – user3489502 Feb 25 '18 at 15:43
Thanks, gettting 'Allowed memory size of 134217728 bytes exhausted' with 'Ticker::latest()->get()' error though, table too large for a sort.. hmm.. about 2M rows. Will have to check this out – user3489502 Feb 25 '18 at 20:11
This isn't the best of ideas, as it'll return every single row from the database for the given Model, hence your memory error. – SlyDave Dec 17 '19 at 12:27

score 0 · Answer 3 · answered Feb 24 '18 at 08:09

0

You may pass multiple arguments to the groupBy method to group by multiple columns

Please refer to documentation https://laravel.com/docs/5.6/queries#ordering-grouping-limit-and-offset

$users = DB::table('users')
            ->groupBy('first_name', 'status')
            ->having('account_id', '>', 100)
            ->get();

answered Feb 24 '18 at 08:09

Hassan Jamal

694
9
11

1

I can group by easily, but then, how is it possible to select the most recent row out of each group returned – user3489502 Feb 25 '18 at 15:39

score 0 · Answer 4 · answered Nov 15 '18 at 14:14

Since Laravel 5.6.17 you can use joinSub() so a possible Eloqunish solution could maybe be something like this:

Group and find the ticket with the last date

    $latest = Ticker::select('exchange', 'base', 'quote', DB::raw('MAX(created_at) as created_at'))
        ->groupBy('exchange', 'base', 'quote');

And join the latest of each group again all records with joinSub()

    $posts = DB::table('tickets')
        ->joinSub($latest, 'latest_tickets', function ($join) {
            $join->on('tickets.exchange', '=', 'latest_tickets.exchange')
                 ->on('tickets.base', '=', 'latest_tickets.base')
                 ->on('tickets.quote', '=', 'latest_tickets.quote')
                  ->on('tickets.created_at', '=', 'latest_posts. created_at');
        })->get();

M Khalid Junaid · Answer 5 · 2020-09-02T06:09:53.383

Here is another way to get latest record per group by using a self left join and this query can be easily transformed to laravel's query builder.

It doesn't require any specific version of laravel to work, it can work on older versions of laravel too
No need for N+1 queries (overhead) as suggested in other answer

In plain SQL it can be written as

select a.*
from tickers a
left join tickers b on a.exchange  = b.exchange
  and a.base = b.base
  and a.quote = b.quote
  and a.created_at < b.created_at 
where b.created_at  is null

And in query builder it would look like

DB::table('tickers as a')
  ->select('a.*')
  ->leftJoin('tickers as b', function ($join) {
        $join->on('a.exchange', '=', 'b.exchange')
             ->whereRaw(DB::raw('a.base = b.base'))
             ->whereRaw(DB::raw('a.quote = b.quote'))
             ->whereRaw(DB::raw('a.created_at < b.created_at'))
             ;
   })
  ->whereNull('b.created_at')
  ->get();

Laravel Eloquent select all rows with max created_at

Or you use a correlated sub query to choose latest row

SQL

select a.*
from tickers a
where exists (
  select 1
  from tickers b
  where a.exchange  = b.exchange
  and a.base = b.base
  and a.quote = b.quote
  group by b.exchange,b.base,b.quote
  having max(b.created_at) = a.created_at
);

Query Builder

DB::table('tickers as a')
   ->whereExists(function ($query) {
       $query->select(DB::raw(1))
             ->from('tickers as b')
             ->whereRaw(DB::raw('a.exchange = b.base'))
             ->whereRaw(DB::raw('a.base = b.base'))
             ->whereRaw(DB::raw('a.quote = b.quote'))
             ->groupBy(['b.exchange','b.base','b.quote'])
             ->havingRaw('max(b.created_at) = a.created_at')
             ;
   })
     ->get();

DEMO

Laravel eloquent get the latest rows of grouped rows

5 Answers5