CODEWITHSUNDEEP
phpmysqldatabaselaraveleloquent
codeforfood
codeforfood

Reputation: 439

Getting rank of row in OrderBy Eloquent query

I understand that this seems to be a documented mySQL question. However, I found no Eloquent solution to get the ranks of rows in an orderBy query. Here is what I want.

I have a table that looks like this

id | number
-----------
1  | 30
2  | 24
3  | 4
4  | 45

I am able to order the number column this query:

Number::orderBy('number', 'desc')->get();

However, I want to retrieve the rank of each row. So the row that has the id of 4 (with the number of 45) would have a rank of 1. The row with the id of 1 (and the number of 30) would have the rank of 2.

What is the best way to get the rank of a row with Eloquent queries?

Upvotes: 1

Views: 3124

Answers (3)

Ley
Ley

Reputation: 29

For laravel's Eloquent... Add this to your model. Unleash the greatness of T.Otwells Eloquent ;)

public function getPostionAttribute()
{
    return $this->newQuery()->where('number', '>=', $this->number)->count();
}

Upvotes: 3

Jarek Tkaczyk
Jarek Tkaczyk

Reputation: 81187

In MySQL you can rely on this (rename row to rank if you like) to get multiple records instead of querying per model:

// any Model, model trait or whatever you like
// for convenience wrapped in scope
public function scopeGetWithRowNumber($query, $columns = ['*'])
{
    // Set the row number
    $offset = (int) $query->getQuery()->offset;
    DB::statement(DB::raw("set @row={$offset}"));

    // Adjust SELECT clause to contain the row
    if ( ! count($query->getQuery()->columns)) $query->select($columns);
    $sub = $query->addSelect([DB::raw('@row:=@row+1 as row')]);

    // Return the result instead of builder object
    return $query->get();
}

Then use however you'd run any query:

Number::getWithRowNumber(); // SELECT * + row attribute starting from 1

Number::take(25)->skip(2500)->getWithRowNumber(); // SELECT * + row 2501+

Number::latest()->getWithRowNumber(['id','number']); // SELECT id, number + row 1+

Number::orderBy('number','desc')->getWithRowNumber(); // example from your question

Upvotes: 1

ollieread
ollieread

Reputation: 6294

Well it's quite simple really, what you currently have will work.

$i = 1;
$numbers = Number::orderBy('number', 'desc')->get();

foreach($numbers as $number)
{
    echo $i . ' ' . $number->id;
    $i++;
}

Upvotes: 0

Related Questions