laravel get row number of a specific record

Question

I have a table queue and I want to know what position or row number a user at.

queue table

----------------------------------------------------
| id  |  name  |        created_at       |   done  |
+-----+--------+-------------------------+---------+
|  1  |  John  |   2020-10-17 01:08:59   |    1    |
|  2  |  Jane  |   2020-10-17 01:10:15   |    0    |
|  3  |  Jess  |   2020-10-17 01:18:15   |    0    |
|  4  |  Joe   |   2020-10-18 08:18:15   |    0    |
|  5  |  Moe   |   2020-10-18 11:18:15   |    0    |
----------------------------------------------------

is it possible to know the specific number of user in queue? for example Jess will return 3 because he's the 3rd user in the queue record.

edit: for example John is done in the queue, now Jess will become the 2nd in the queue.

`$q = Queue::where('name', 'Jess')->first();` then `$id = $q->id;` `$id` will return `3` — STA, Oct 17 '20 at 14:19
is there any approach for that? what if it's a different `id`? — Konjesh Esbrading, Oct 17 '20 at 14:20
You mean, if you delete the `John` then, `Jess` will return `2`, instead of `3`? Because, `Jess` on the second position now — STA, Oct 17 '20 at 14:23
Does this answer your question? [Laravel how do I get the row number of an object using Eloquent?](https://stackoverflow.com/questions/23408091/laravel-how-do-i-get-the-row-number-of-an-object-using-eloquent) — OMR, Oct 17 '20 at 14:52
@OMR I tried with `Product::selectRaw('*, @row:=@row+1 as row')->get();` but all `row` returns null — STA, Oct 17 '20 at 14:57
@konjesh you may try on your side, idk why all row returns null here — STA, Oct 17 '20 at 15:14
@OMR that's not working, it think it's because it's an answer from `2014` — Konjesh Esbrading, Oct 17 '20 at 15:32

OMR · Answer 1 · 2020-10-19T05:59:15.770

3

I think I have a way:

the main idea is to get the count of the previous queue based on their id values, and the trick is to use an alias to the main table so you can use that alias in your internal select.

   $values = DB::table('queue', 'u1')
            ->select('u1.id',DB::raw("((SELECT count(*) from queue WHERE queue.id < u1.id)+1) rowNumber"))
            ->orderBy('u1.id')
            ->get();

Edit:

if you want to exclude done queue you should do it in the main and the internal select:

 $values = DB::table('queue', 'u1')
            ->select('u1.id',DB::raw("((SELECT count(*) from queue WHERE (queue.id < u1.id)and(queue.is_done!=1) )+1) rowNumber"))
            ->where('u1.is_done','!=',1)
            ->orderBy('u1.id')
            ->get();

edited Oct 19 '20 at 05:59

answered Oct 17 '20 at 19:18

OMR

11,736
5
20
35

this is a little bit close to what I am trying to achieve. actually I need to filter `done = 0`, but when I try in based on your answer, it still preserved the number of row where he is, not calculating where he was at after the `done` user in queue, I mean I need to recount and adjust the `rowNumber` once a user is done in queue – Konjesh Esbrading Oct 18 '20 at 19:39
I mean, I need to exclude the `done` in queue they're `NOT` be counted in `rowNumber` – Konjesh Esbrading Oct 18 '20 at 20:41
1

This is a nice solution. Once you pair it with your Eloquent model as a scope and build the subquery using the syntax `$query->addSelect(['position' => ...build query here...])` you will have an elegant solution. – Pablo Oct 19 '20 at 04:50

score -1 · Answer 2 · answered Oct 17 '20 at 16:37

-1

If you don't need to get that number directly with query you can do search()

$collection->search(function ($item, $key) {
    return $item->name == 'Jess';
});

answered Oct 17 '20 at 16:37

Osta

147
5

Thank you for answering @Osta, but I think you don't understand the question fully. – Konjesh Esbrading Oct 17 '20 at 16:53

laravel get row number of a specific record

2 Answers2