CODEWITHSUNDEEP
mysqllaravel
Phil
Phil

Reputation: 2246

how to maintain queue number in sql

I have a waitlist table with a many to many relation to an attendee table.

Basically, a list of attendees in a waitlist...I need to store where each attendee is on the waitlist. I.E. order_number is a auto-incremental unique int: 1,2,3,4,5,6...

Waitlist_Attendee Table
id
attendee_id_fk
waitlist_id_fk
order_number

My problem is if I have a bunch of attendees in a waitlist, how do I update the order_number if I later remove an attendee from the waitlist? Say order_number=1 gets deleted from the waitlist. I need to update order_number=2 so it is now order_numer=1 and continue on for each order_number and so forth...

Is there an SQL method to auto-reoder the numbers so it is always a sequential list? or do I need to write a sort function to number them?

Or is there a better way I am not aware of?

Upvotes: 0

Views: 446

Answers (2)

Alister Bulman
Alister Bulman

Reputation: 35169

Have you considered that you may not need to re-number? The number that is shown beside the list on output is a display-time event. As long as the list is created in ascending order, even if one is removed, then you can still select the top 10, even if the numbers go from 1 to 12, and #2 and #10 had been deleted for example. That is just a LIMIT 10 ORDER BY id ASC - and a loop that counts 1 to 10 for display (or even just show ten items with OL and LI tags to do the numbering in HTML).

Upvotes: 1

dakine
dakine

Reputation: 699

I guess you just need to loop thru your attendees and change the order number. 

$waitList = WaitList::with(['attendees' => function($q){
$q->orderBy('order_number');
})->find(1);

$count = 1;
foreach($waitList->attendees as $attendees)
{
 WaitList::find(1)->attendees()->updateExistingPivot($attendees->id, ['order_number' => $count]);
$count++;
}

Upvotes: 1

Related Questions