Create a randomly ordered playlist from the song table and return the name of the current, next and previous song?

Question

Consider the following table:

Song ---- SongID GUID Primary Key Name Varchar dateAdded DateTime 

I am creating a website that plays songs from the Song table. I want to create a randomly ordered playlist for each visitor to the site that is stored in requests without using server storage (without session style or database storage). The player has the buttons "Play", "Next song" and "Previous song". What is the most efficient way to store data in all queries? What is the most effective query for this information? I have a solution (in mySql) that works, but I'm looking to see if something works more efficient. In addition, I do not want to bias the answers to using my solution. Please add a request to your response.

Answer 1

Update: here the requirements that I followed are spelled out:

• Regardless of how many songs are in the table or at the current location in the playlist, the information that the client must store (for example, in a cookie) must be of a constant size.
• Using "next" 100 times, followed by "prev" 100 times, should result in some sequence of 100 songs (possibly including duplicates), after which this exact sequence will be canceled regardless of any insert that could be made between any "closest" or "prev". ("Previous song always means previous song.")
• A song that would be on the playlist “when it was generated / initialized” and has since been deleted will be skipped.
  • It will not be difficult to change my answer below to return an indicator not found.

---

I think you need one more piece of information: an additional key for the song of the whole position, in addition to your GUID (or you can replace it with this). Then, in combination with PRNG , you can do the simplest and fastest search. Pseudocode:

 def next_song(initial_seed, current_prng_index, high_song_index): """Returns the next song and the parameters to be later passed to next/prev_song.""" while True: current_prng_index += 1 current_seed = PRNG_advance(initial_seed, current_prng_index) song_index = song_index_from_seed(current_seed, high_song_index) song_id = (SELECT SongID FROM Songs WHERE SongIndex=song_index) if song_id: # test, somehow, for non-deleted songs return song_id, (initial_seed, current_prng_index, high_song_index) # include values that must be passed on the next call # prev is the same, except uses -= 1 def song_index_from_seed(seed, highest): return seed % (highest + 1) # simple, but better possibilities might exist for your data def new_playlist(): """Returns the parameters to be passed to next/prev_song.""" high_song_index = (SELECT MAX(SongIndex) FROM Songs) return get_a_random_number(), 0, high_song_index 

This will gradually slow down in each next song and will not allow you to wrap it, coming back (without any creativity). If you find a suitable reversible PRNG (although this is rare among good PRNGs, AFAIK):

 def next_song(current_seed, high_song_index): while True: current_seed = PRNG_next(current_seed) song_index = song_index_from_seed(current_seed, high_song_index) song_id = (SELECT SongID FROM Songs WHERE SongIndex=song_index) if song_id: # test, somehow, for non-deleted songs return song_id, (current_seed, high_song_index) # include values that must be passed on the next call # prev is the same, except uses PRNG_prev 

This handles the deletion by skipping these songs (or if you never delete it is not even a problem), but otherwise it does not change the order, no matter how many are deleted. The inserts are processed by the song_index_from_seed function, restraining the index, so new songs are never “visible”. It is also an endless loop if all available songs have been deleted, but I think this code handles all other corner cases.

(Refactoring next / previous versions for simple wrappers around a common function is not difficult, but there is no point in increasing clarity.)

I have effectively replaced your dateAdded with my position index, but this is an improvement since you do not need to save deleted rows as mannequins (but still cannot reuse their index), as if the position index for the order for the date Added has been calculated.

Using PRNG, as it seems naive, but works; and you have to be careful that the selected combination of PRNG and song_index_from_seed behaves as you expect: it is possible that some source samples generate "non-random" playlists. (Not because they are not random, but because listeners expect a combination of songs instead of 4, 4 , 9, 9 , ...)

Answer 2

Here is an idea that will do pseudo-random ordering if SQL. I do not have a database to verify this right now, so the actual SQL may need to be changed. I also assume your identifier column is an integer.

Choose two multi-digit primes in random order, P1 and P2, where P2 is greater than P1.

Select * from the composition order (mod ((ID * P2), P1)), ID

The idea is to multiply the ID by one prime and then perform the mod operation using P1. This should properly shuffle the songs, and each right pair will shuffle them differently.

To recreate the exact order, you only need to have P1 and P2, which should be just a few characters. No problems sending back and forth during the request. You may need to experiment a little with your prime number range, but I would suggest that using all 4,5,6 digits prime should give you a pretty large number of wounds.

Answer 3

You can use a pseudo-random sequence . Store gseed and playlist position in a cookie. Pseudo-random seed ensures that you recreate the same sequence of playlists for each request without storing the actual playlist on the server.