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

Answer 1

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().