Laravel Eloquent Select All Rows With Max Created_At

Laravel Eloquent select all rows with max created_at

To get latest record for each seller_id you can use following query

select s.*
from snapshot s
left join snapshot s1 on s.seller_id = s1.seller_id
and s.created_at < s1.created_at
where s1.seller_id is null

Using query builder you might rewrite it as

DB::table('snapshot as s')
  ->select('s.*')
  ->leftJoin('snapshot as s1', function ($join) {
        $join->on('s.seller_id', '=', 's1.seller_id')
             ->whereRaw(DB::raw('s.created_at < s1.created_at'));
   })
  ->whereNull('s1.seller_id')
  ->get();

Eloquent get only latest per value on column

I think this will give you desired output. We first group files by their group, then we get only the latest records by the updated_at column:

$files = Model::groupBy('group')->latest('updated_at')->get();

Get Min and Max of rows that are grouped by Day

You need to also group by the day:

$rates = Rate::select(DB::raw("DATE_FORMAT(created_at, %Y-%m-%d) day"))
    ->selectRaw("MIN(sell_rate) AS min_rate, MAX(sell_rate) AS max_rate")
    ->where('market_currency_id', '=', 44)
    ->whereBetween('created_at', ['2020-10-10', '2020-10-11'])
    ->groupBy(DB::raw('day'))
    ->orderBy(DB::raw('day'))
    ->get()
    ->toArray();

Laravel eloquent get the latest rows of grouped rows

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

---

Related Topics