CODEWITHSUNDEEP
mysqllaravel
Konjesh Esbrading
Konjesh Esbrading

Reputation: 404

laravel get row number of a specific record

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.

Upvotes: 1

Views: 714

Answers (2)

OMR
OMR

Reputation: 12208

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

Upvotes: 3

Osta
Osta

Reputation: 167

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';
});

Upvotes: -1

Related Questions