I was assigned the task of making a secret santa site where a user logs in and a random name is chosen from a database. The condition being that u don't draw your own name but since a single name is picked at a time, I ran into the below mentioned issue: 3 people are (bill,mike and jake) bill < mike Mike < bill If mike and bill get each other, jake won't have a partner. I could write a small piece of code that avoids this when coming down to a small number of people, I was wandering if their is anything else to get around this issue. My current php code

$sql ="SELECT gifted 
        FROM gifts 
        WHERE gifted != '$username' 
        AND isgifted = 0 
        ORDER BY Rand() 
        LIMIT 1";

CodePudding user response：

I think a simple algorithm which solves this problem would be:

1. Get a list of all the participants, sorted in a random order with the SQL rand() function.
2. For each row in the randomized list, assign each person a secret santa from the next row. For instance, row 1 would be assigned row 2 as his/her santa. Row 2 would be assigned row 3, and so on. Use the sql lead() function for this.
3. For the last row in the list, loop back around and assign the first row as his/her santa. Use the first_value() function for this.

It would probably be easiest to do this in memory in your PHP application, but since this question is mostly concerned with SQL, here's a pure SQL solution. Assuming you have a table called "people" with columns "name" and "id" this will give you the result you want:

SELECT name,
(CASE
  WHEN secret_santa IS NULL
  THEN first
   ELSE secret_santa
END) secret_santa
FROM (
  SELECT name,secret_santa,(FIRST_VALUE(name) over ()) as first FROM (
    SELECT name,id,secret_santa
    FROM (
      SELECT name, id, LEAD(name) OVER (ORDER BY RAND()) secret_santa
      FROM people
    ) santas
   ) randomized
 ) wraparound

Output:

 ------ -------------- 
| name | secret_santa |
 ------ -------------- 
| Mike | Jake         |
| Jake | Bill         |
| Bill | Mike         |
 ------ -------------- 

Note, all of the complexity here comes from the need to wrap the last row around to the first. If you were willing to tolerate one person with no santa to be fixed with custom code, then the SQL is just:

SELECT name, LEAD(name) OVER (ORDER BY RAND()) secret_santa
FROM people

Output:

 ------ -------------- 
| name | secret_santa |
 ------ -------------- 
| Bill | Mike         |
| Mike | Jake         |
| Jake | NULL         |
 ------ -------------- 

Working Example Here