Laravel how do I get the row number of an object using Eloquent?

Question

I'd like to know the position of a user based on its creation date. How do I do that using Eloquent?

I'd like to be able to do something like this:

User::getRowNumber($user_obj);

Answer 1

I suppose you want MySQL solution, so you can do this:

DB::statement(DB::raw('set @row:=0'));
User::selectRaw('*, @row:=@row+1 as row')->get();
// returns all users with ordinal 'row'

So you could implement something like this:

public function scopeWithRowNumber($query, $column = 'created_at', $order = 'asc')
{
    DB::statement(DB::raw('set @row=0'));

    $sub = static::selectRaw('*, @row:=@row+1 as row')
        ->orderBy($column, $order)->toSql();

    $query->remember(1)->from(DB::raw("({$sub}) as sub"));
}

public function getRowNumber($column = 'created_at', $order = 'asc')
{
    $order = ($order == 'asc') ? 'asc' : 'desc';

    $key = "userRow.{$this->id}.{$column}.{$order}";

    if (Cache::get($key)) return Cache::get($key);

    $row = $this->withRowNumber($column, $order)
        ->where($column, '<=',$this->$column)
        ->whereId($this->id)->pluck('row');

    Cache::put($key, $row);

    return $row;
}

This needs to select all the rows from the table till the one you are looking for is found, then selects only that particular row number.

It will let you do this:

$user = User::find(15);

$user->getRowNumber(); // as default ordered by created_at ascending

$user->getRowNumber('username'); // check order for another column

$user->getRowNumber('updated_at', 'desc'); // different combination of column and order

// and utilizing the scope:
User::withRowNumber()->take(20)->get(); // returns collection with additional property 'row' for each user

As this scope requires raw statement setting @row to 0 everytime, we use caching for 1 minute to avoid unnecessary queries.

Answer 2

$query = \DB::table(\DB::raw('Products, (SELECT @row := 0) r'));
$query = $query->select(
                            \DB::raw('@row := @row + 1 AS SrNo'),                                
                            'ProductID', 
                            'ProductName', 
                            'Description',                 
                             \DB::raw('IFNULL(ProductImage,"") AS ProductImage')
                        );

// where clauses
if(...){
    $query = $query->where('ProductID', ...));
}

// orderby clauses
// ...
// $query = $query->orderBy('..','DESC');

// count clause
$TotalRecordCount = $query->count();

$results =  $query
                ->take(...)                
                ->skip(...)
                ->get();

Answer 3

I believe you could use Raw Expresssions to achieve this:

$users = DB::table('users')
         ->select(DB::raw('ROW_NUMBER() OVER(ORDER BY ID DESC) AS Row, status'))
         ->where('status', '<>', 1)
         ->groupBy('status')
         ->get();

However, looking trough the source code looks like you could achieve the same when using SQLServer and offset. The sources indicates that if you something like the following:

$users = DB::table('users')->skip(10)->take(5)->get();

The generated SQL query will include the row_number over statement.

Answer 4

[For Postgres]

• In your model

  public function scopeWithRowNumber($query, $column = 'id', $order = 'asc'){ $sub = static::selectRaw('*, row_number() OVER () as row_number') ->orderBy($column, $order) ->toSql(); $query->from(DB::raw("({$sub}) as sub")); }

• In your controller

  $user = User::withRowNumber()->get();