Eloquent/SQL - Get the row number of a query (rank)

Question

I would like to know the rank based on my DB structure:

I have a model Post that belongs to a model called Edition (also one Edition has many Post).

One Post has many Like.

I would like to know the rank of a Post based on the Like count inside a particular Edition.

The code:

// Define the id of an edition
$edition_id = 53;
// The id of the post to retrieve rank
$post_id = 132;
// Compose the query by select all posts, of interested edition ...
$query = App\Models\Post::where('edition_id', $edition_id)
    // ... with like count (this produce an additional field named likes_count) ...
    ->withCount('likes')
    // ... in descendig order by this count.
    ->orderBy('likes_count', 'desc');
// By execute it I can get right results.
$query->get();

For people who are not familiar with Laravel Eloquent ORM, I report the sql query executed from code above:

select `posts`.*, (select count(*) from `likes` where `posts`.`id` = `likes`.`post_id` and `likes`.`deleted_at` is null) as `likes_count`
from `posts`
where `edition_id` = '53' and `posts`.`deleted_at` is null
order by `likes_count` desc

I report the query results:

=> Illuminate\Database\Eloquent\Collection {#994
all: [
    App\Models\Post {#993
        id: 135,
        likes_count: 4,
    },
    App\Models\Post {#1075
        id: 134,
        likes_count: 3,
    },
    App\Models\Post {#1091
        id: 133,
        likes_count: 2,
    },
    App\Models\Post {#997
        id: 132,
        likes_count: 1,
    },
    App\Models\Post {#1038
        id: 131,
        likes_count: 0,
    },
],
}

How can I get the row position of the record with a certain id from the results of the composed query?

For example, how to retrieve the rank result of the record with id = 132?

@JonasStaudenmeir: In my development env I have `mysql Ver 14.14 Distrib 5.7.19, for macos10.12 (x86_64) using EditLine wrapper`. — Tenaciousd93, Apr 01 '19 at 10:30
@JonasStaudenmeir thanks for your reply. Yes, I've seen it and I read about `ROW_NUMBER()` but I can't implement it in the right way. I do the query with the right row number, but if I add the filter by id the result is always 1, not the number of the rank that was before the `where id = X` filter. — Tenaciousd93, Apr 02 '19 at 07:16
In the questions at the end, I wrote `How can I get the row position of the record with a certain id from the results of the composed query?` because I need to know the position for each post I load. — Tenaciousd93, Apr 02 '19 at 07:58
What does your use case look like? Do you want to retrieve the edition's posts with their respective ranks? Or do you only want to get the rank of a single post and don't actually need all the other posts from `$query->get()`? — Jonas Staudenmeir, Apr 02 '19 at 08:33
Currently I build a serializer method in my post model that will be called automatically on each post returned to fronted: my idea was to calculate the rank in this position. However I have to fetch a list of post to be return paginated. So, I can add the rank calculation in both places: maybe where is simple or where have better performances. — Tenaciousd93, Apr 02 '19 at 09:03

Jonas Staudenmeir · Accepted Answer · 2019-04-02T15:45:22.003

2

You can use a subquery:

$query = Post::where('edition_id', $edition_id)
    ->withCount('likes')
    ->selectRaw('@rank := @rank + 1 rank')
    ->from(DB::raw('`posts`, (SELECT @rank := 0) vars'))
    ->orderBy('likes_count', 'desc');

$posts = Post::fromSub($query, 'posts')->paginate();

$post = Post::fromSub($query, 'posts')->find($post_id);

Workaround for Laravel < 5.6:

Builder::macro('fromSub', function($query, $as) {
    $this->bindings = array_merge(
        array_slice($this->bindings, 0, 1),
        ['from' => $query->getBindings()],
        array_slice($this->bindings, 1)
    );

    $sql = '('.$query->toSql().') as '.$this->grammar->wrap($as);

    return $this->from(DB::raw($sql));
});

edited Apr 02 '19 at 15:45

answered Apr 02 '19 at 09:37

Jonas Staudenmeir

24,815
6
63
109

Thanks for your response! I tried but my Laravel version is the last LTS version `5.5.34`. The methods `fromRaw ` and `fromSub` are available from version `5.6`: is there any alternative? If not I will read the upgrade guide, but I would like to stay on a LTS version. – Tenaciousd93 Apr 02 '19 at 14:01
I've replaced `fromRaw()` and added a workaround for Laravel 5.5. You can implement `fromSub()` with a macro. – Jonas Staudenmeir Apr 02 '19 at 15:46
Hi Jonas, excuse me for the late, I'm planing update laravel to 5.6 to avoid introducing complexity to the search engine with the macro solution you have posted. I didn't try it, but tomorrow I will deploy the current version and try the subquery method. Do you know if there are any performance issues using these queries? – Tenaciousd93 Apr 08 '19 at 08:38
No, the queries should be reasonably fast. – Jonas Staudenmeir Apr 08 '19 at 08:41
Hi Jonas, sorry for the late, I was very busy. Yesterday I've update laravel to version 5.6.X and I tried to implement your solution. It works great! Thank you very much for the awesome help. – Tenaciousd93 May 07 '19 at 13:24

score 0 · Answer 2 · answered Mar 26 '19 at 17:46

0

You can use the search() method to find the key:

The search method searches the collection for the given value and returns its key if found. If the item is not found, false is returned.

$id = 132;

$key = $query->search(function($post,$id) {
    return $post->id === $id;
});

answered Mar 26 '19 at 17:46

Piazzi

2,490
3
11
25

1

Hi Lucas, thanks for your reply! The `search` method searches on `collection`, as you say. This means 2 things: 1) cause a `Call to undefined method Illuminate\Database\Query\Builder::search()` on my query builder: to get work I have to execute it and get result into a collection with `->get()`method. 2) By execute it means that I have to iterate each objects inside the collection results: if I have many hundreds or thousands posts inside the edition it guess it will be poorly performing in terms of timing and memory usage. – Tenaciousd93 Mar 27 '19 at 05:08
Yes, you're right, you have to call the ```get``` to make it work and probably wont be the most optimazed thing, but is the only answer i could think for your case, i hope it help you in any way :) – Piazzi Mar 27 '19 at 12:17

Eloquent/SQL - Get the row number of a query (rank)

2 Answers2