mysql query help

[ukscotth]

Hi all,

 

I have a database with 2 tables, 'users' and 'battles'. The site pulls 2 random peoples pictures and lets the user choose who they think would win the battle. So if user1 is using the site it might show pictures for user5 and user3. If the user1 chooses that user5 wins then an entry is made into the 'battles' table like this :

 

voter    win      lose

user1    user5  user3

 

Any ideas what query I can use so it only shows 2 people that the user hasnt compared before ?

 

As if its doing this : choose 2 id's from 'users' that user1 hasnt compared before

 

Hope that makes sense.

 

Many thanks,

 

Scott

[Psycho]

It would have been VERY helpful if you had given some details about the tables and the fields. I will assume that in your example above you area ctaully storing the ID of the users and not the username. Because, if you are doing otherwise you are doing it wrong.

 

Anyway, the following query will get you two random records that the user has not submitted a vote for. NOTE: Using ORDER BY RAND() will be problematic if you have a LOT of records. If you think you will have a lot of users, then do the research to get random records quickly.

 

SELECT `userID`
FROM `users`
WHERE `userID` NOT IN (SELECT `win`
                       FROM `battles`
                       WHERE `voter` = '$thisUserID')
  AND `userID` NOT IN (SELECT `lose`
                       FROM `battles`
                       WHERE `voter` = '$thisUserID')
ORDER BY RAND()
LIMIT 2

[xyph]

Is this for fun or do you expect tens of thousands of users? If the latter, then you really want to get an SQL expert to do this. Grabbing multiple random values from a large table is tricky to do properly. You then have to have a large subquery that goes through the entire battle table, which could be hundreds of thousands of rows large.

[xyph]

Oh, and @mjdamato

 

NOT IN is not an alias for <> ANY, but for <> ALL.

http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

[ukscotth]

Thanks you 2 Il give that a try when can kick my neice off the computer. Yes it uses id's and not usernames. Its for a facebook app, if it starts to get busy then I will do some research about how to do the random part more efficiently. Thanks again for the help.

[ukscotth]

just tried that code and it looks like its not pulling any records if either of the 2 ids are either in 'win' or 'lose' instead of not pulling the ones that contain both. If that makes sense lol

[Psycho]

Oh, and @mjdamato

 

NOT IN is not an alias for <> ANY, but for <> ALL.

http://dev.mysql.com/doc/refman/5.0/en/any-in-some-subqueries.html

 

OK, not sure what you are getting at. That was my intention. If it was some_value <> any (1, 2, 3) that would ALWAYS result in true, because if you are comparing if one value is not equal to two or more values, the original value could only equal one of the target values at most, so it would always not be equal to N or N-1 values (where N is the number of target values)

 

just tried that code and it looks like its not pulling any records if either of the 2 ids are either in 'win' or 'lose' instead of not pulling the ones that contain both. If that makes sense lol

No, it doesn't. I ran a test as follows:

 

User table

userID  name
1       One
2       Two
3       Three
4       Four
5       Five
6       Six
7       Seven
8       Eight

 

Battles table

voter 	win 	lose
1        2       3
1        4       5

 

So, user 1 has selected users 2, 3, 4,  & 5. When I run the query above without the LIMIT clause I get 1, 6, 7, & 8 which are the user IDs that user 1 has not used (I suspect you also want to exclude the user's own ID though). When I use the LIMIT clause I get two values from that set.

 

You seemed to be saying you only wanted to exclude IDs that were not used for both the win and the lose columns. That doesn't make sense. You could have two ids that were both used for lose. If the user gets those two, they cannot make a selection that doesn't create a duplicate entry for user 1 in the lose column.

[ukscotth]

Thanks for the response, sorry if im being confusing, hopefully this will make more sense.

 

If Jack has to choose between Sue and Pam and he chooses Sue he still wants Sue included in future choices and he still wants Pam to be included in future choices, he just doesnt want them both to come up again at the same time as he has already chosen Sue over Pam.

 

[xyph]

I misread, my mistake.

[ukscotth]

any ideas anyone ? im stumped

[Psycho]

The only thing I can think of is probably a pretty inefficient solution, but  you'll have to see if it meets your needs with respect to performance and scalability.

 

The idea goes something like this: create a dynamic table of all user ID pairs (concatenated), then select from that table any pair which does not exist in the current pairs (concatenated) from the selections the user has made previously.

 

NOTE: I have not tested any of the following since I don't have the test database I used before and I'm not going to take the time to re-build it. I am going to provide the step-by-step portions so you can test as needed.

 

So, working from the beginning, this query should give you a result of all "unique" user ID combinations.

SELECT u1.userID AS id1, u2.userID AS id2
FROM `users` AS u1
JOIN `users` AS u2
  ON u1.userID < u2.userID

 

So, if you have four user ID: 1, 2, 3, 4 that should return the results: 1-2, 1-3, 1-4, 2-3, 2-4, and 3-4

 

The next step is to select a value from the result above where the same pair does not exist for a previous selection. But, the win/lose columns are not in order of smallest to highest, so we will need to get a little creative. So, this query should give you all the pairs that the user have previously compared in the same format as above. This query should get you all the user iD pairs for a particular user

SELECT CONCAT(IF(win<lose, win, lose), '-', IF(win<lose, lose, win)) as userid_pair
FROM `battles`
WHERE `voter` = '$thisUserID'

 

Now, we need to put those two queries together such that we want to select a value from the first query that does not exist in the second query. Again, this is not tested, but give it a go.

SELECT u1.userID AS id1, u2.userID AS id2
FROM `users` AS u1
JOIN `users` AS u2
  ON u1.userID < u2.userID
WHERE CONCAT(u1.userID, '-', u2.userID) NOT IN (
    SELECT CONCAT(IF(win<lose, win, lose), '-', IF(win<lose, lose, win)) as pair
    FROM `battles`
    WHERE `voter` = '$thisUserID')
ORDER BY RAND()
LIMIT 2

 

OK, I lied. There was a problem with my first approach, so I had to change it. So, I did test this and it works. But, again, not sure if there is a more efficient method or not.

[ukscotth]

Thanks very much, il try and get my head around it lol