Hello I am building a playlist system. People can create a playlist and add podcast episodes to it.
Now I need to allow ordering of episodes in playlist, for that purpose i added order column to my playlist_episodes pivot table.
But now when people delete episodes from playlist i will have gaps in order column.
Because if i had three items in my playlist with order 1, 2, 3 and then i deleted second item my playlist would have order 1, 3.
Seems like this gap could be a reason for future bugs.
Is there a best pactice way to go around this problem or to avoid it?
I was thinking about changing order of all episodes whenever one is deleted, but that seems like an overkill.
You could use events to listen on the deleted-event and then re-set the order-column in your pivot table to remove the gaps.
I was thinking about changing order of all episodes whenever one is deleted, but that seems like an overkill.
If you think about it, this is exactly what you want. Remove the gaps. Therefore reorder all other episodes. At least the ones after the current one. This could probably be done with a single database query.
Example:
You delete the episode with the order of 5. Then you could simply run a query:
UPDATE pivot_table SET order = order - 1 WHERE order > 5 AND playlist_id = ?
How error prone this is depends on your application. But it is a quick and easy way to manage this.
