RE: Question on sub-selects

[Jeffrey Smelser]

 Hello,
 
   I have a question that someone here may or may not be 
 able to answer (I
 think perhaps MySQL is incapable of a solution). I have a 
 program which
 uses a MySQL database to help create a playlist for a 
 ShoutCast stream.
 There is a web site associated with the web-radio. Users of 
 the web site
 can rate songs which are contained in the database. The rating system
 works such that users can rate songs from +3 to -2. Now, what I would
 like to accomplish is to create a query that allows me to randomly
 select a song from the database to add to the queue while taking into
 account the ratings. For example:
 
 There are 2400 songs listed
 One song has been rated three times as follows:
   +3
   +1
   -1
 I would like that song to have a 3/2400 chance of being 
 selected for the
 queue.
 Secondly, I need the database to store who voted for waht so 
 that users
 cannot continually vote +3 over and over for their favorite song.

Are you trying to mimic launchcast?? I be interested in this.. I am looking for a 
player like that.

  I know how I can accomplish this in Postgres using VIEWs, but I have
 not yet been able to find a good solution in MySQL. Can 
 anyone recommend
 a viable option? My current database structure is irrelevant, 
 as I will
 change the structure if I can find a good solution.

well, first, why the numbering scheme? I would think it be hard to devise it with 
negative numbers.. or is -1 basically x'ing it out?? 

Thanks,
Jeff

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Question on sub-selects

[Tobias Asplund]

On Wed, 28 Jan 2004, Deven Phillips wrote:

 Hello,

 There is a web site associated with the web-radio. Users of the web site
 can rate songs which are contained in the database. The rating system
 works such that users can rate songs from +3 to -2. Now, what I would
 like to accomplish is to create a query that allows me to randomly
 select a song from the database to add to the queue while taking into
 account the ratings. For example:

 There are 2400 songs listed
 One song has been rated three times as follows:
   +3
   +1
   -1
 I would like that song to have a 3/2400 chance of being selected for the
 queue.

You can easily come into a situation where this isn't feasible.
I assume the 3 in 3/2400 is the sum of the votes.
Imagine you have 2 songs and 10 users.
5 users vote 3 for one of the songs and 5 users vote 2 for the same song.
Then you'd have 25/2 chance of having that song selected.


 Secondly, I need the database to store who voted for waht so that users
 cannot continually vote +3 over and over for their favorite song.

That is easily accomplished, just use a table like:

CREATE TABLE votes (
  userid INT UNSIGNED NOT NULL,
  songid INT NOT NULL,
  vote TINYINT NOT NULL,
  PRIMARY KEY  (userid, songid)
)

Then when your web application inserts or updates a vote you can use
REPLACE INTO votes (userid, songid, vote) VALUES(1, 1, 2);
That will take care of the problem of someone voting for their favorite
song more than once.



One suggestion (doesn't have the percentages that you specify above, but
that is easily implementable) could be to use the votes table above and
the songs table:

CREATE TABLE songs (
  songid INT UNSIGNED NOT NULL AUTO_INCREMENT,
  artistid INT UNSIGNED NOT NULL,
  songname char(75) NOT NULL,
  PRIMARY KEY  (songid)
)


Then you could use a query like:

SELECT songname, RAND() * (AVG(votes.vote) + 10) AS rnd
FROM songs
JOIN votes ON votes.songid = songs.songid
GROUP BY songs.songid
ORDER BY rnd DESC
LIMIT 1

This will generate random numbers between 0 and 8-13 depending on what the
average vote for the song is, you can change the + 10 to any number to
weigh them differently (although, if you use 0-2 votes with low averages
might never be played since their values will always be negative).

Might give you some new ideas if nothing else

cheers,
Tobias

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]