Thread: Secret Santa List

I have a company with four employees who participate in a Secret Santa
program, where each buys a gift for an employee chosen at random.  (For
now, I do not mind if an employee ends up buying a gift for himself.)
How can I make this work with an SQL statement?

Here is my Secret Santa table:

--
create table secretsanta
(giver text,
recipient text,
primary key (giver));

insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
('Earl');
--

Here is the SQL statement I am using to populate the "recipient" column:

--
update secretsanta set recipient =
( select giver from secretsanta s2 where not exists (select * from
secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
--

The problem: every time I run this, a single name is chosen at random
and used to populate all the rows.  So all four rows will get a
recipient of "Steve" or "Earl" or whatever single name is chosen at random.

I suppose the problem is that the "exists" subquery does not re-evaluate
for each record.  How do I prevent this from happening? Can I use a
"lateral" join of some kind, or somehow tell PostgreSQL to not be so
optimized?

On 23 December 2015 at 16:49, Lou Duchez <lou@paprikash.com> wrote:

> I have a company with four employees who participate in a Secret Santa
> program, where each buys a gift for an employee chosen at random.  (For
> now, I do not mind if an employee ends up buying a gift for himself.)  How
> can I make this work with an SQL statement?
>
> Here is the SQL statement I am using to populate the "recipient" column:
>
> --
> update secretsanta set recipient =
> ( select giver from secretsanta s2 where not exists (select * from
> secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
> --
>
> The problem: every time I run this, a single name is chosen at random and
> used to populate all the rows.  So all four rows will get a recipient of
> "Steve" or "Earl" or whatever single name is chosen at random.

Of course: you can't UPDATE a field with a query returning more than one
result, as you can check easily trying:

update secretsanta set recipient=(select giver from secretsanta);


You could get a list of givers in no particular order (e. g. "select giver
from secretsanta order by md5(concat(giver,current_time))") then setting
each employee as next's employee giver.

--
Alberto Cabello Sánchez
Universidad de Extremadura

> Of course: you can't UPDATE a field with a query returning more than one
> result, as you can check easily trying:

I understand that, and my query does not return more than one result.  The problem is that it returns THE SAME result
eachtime, most likely because the subquery is evaluated exactly once and then the main query uses that single result
overand over. 

update secretsanta set recipient =
( select giver from secretsanta s2 where not exists (select * from
secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );

My hope is to somehow persuade PostgreSQL to re-evaluate the subquery each time, and see that the set of available
recipientshas changed.  If "Steve" was picked for the first row, "Steve" shouldn't be available for any subsequent row.
If "Fred" was picked for the second row, neither "Steve" nor "Fred" should be available for any subsequent row. 

> You could get a list of givers in no particular order (e. g. "select giver
> from secretsanta order by md5(concat(giver,current_time))") then setting
> each employee as next's employee giver.

As in, write a loop in some programming language to update the table one row at a time, or did you envision a way to do
thiswith an SQL statement?  I can certainly write a loop, if that's the only solution. 

Thanks!

On Wed, Dec 23, 2015 at 04:32:34AM -0500, Lou Duchez wrote:
> >Of course: you can't UPDATE a field with a query returning more than one
> >result, as you can check easily trying:
>
> I understand that, and my query does not return more than one result.  The
> problem is that it returns THE SAME result each time, most likely because
> the subquery is evaluated exactly once and then the main query uses that
> single result over and over.
>
> update secretsanta set recipient =
> ( select giver from secretsanta s2 where not exists (select * from
> secretsanta s3 where s3.recipient = s2.giver) order by random() limit 1 );
>
> My hope is to somehow persuade PostgreSQL to re-evaluate the subquery each
> time, and see that the set of available recipients has changed.

I see. As in most programming languages, the () clause is evaluated once
and the result used instead.

> >You could get a list of givers in no particular order (e. g. "select giver
> >from secretsanta order by md5(concat(giver,current_time))") then setting
> >each employee as next's employee giver.
>
> As in, write a loop in some programming language to update the table one row
> at a time, or did you envision a way to do this with an SQL statement?  I can
> certainly write a loop, if that's the only solution.

I'm not aware of a SQL statement to do that. Maybe you will be able do it with
a CTE, as you can make a table with a field (1, 2, 3, 4) and take advantage of
the integer arithmetic (one problem with your original question is I don't know
how to ask for the next employee in plain SQL when the ID is a given name).

--
Alberto Cabello Sánchez
Universidad de Extremadura

Lou Duchez schrieb am 23.12.2015 um 04:49:
> I have a company with four employees who participate in a Secret
> Santa program, where each buys a gift for an employee chosen at
> random.  (For now, I do not mind if an employee ends up buying a gift
> for himself.) How can I make this work with an SQL statement?
>
> Here is my Secret Santa table:
>
> -- create table secretsanta (giver text, recipient text, primary key
> (giver));
>
> insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
> ('Earl'); --
>
> Here is the SQL statement I am using to populate the "recipient"
> column:
>
> -- update secretsanta set recipient = ( select giver from secretsanta
> s2 where not exists (select * from secretsanta s3 where s3.recipient
> = s2.giver) order by random() limit 1 ); --
>
> The problem: every time I run this, a single name is chosen at random
> and used to populate all the rows.  So all four rows will get a
> recipient of "Steve" or "Earl" or whatever single name is chosen at
> random.
>
> I suppose the problem is that the "exists" subquery does not
> re-evaluate for each record.  How do I prevent this from happening?
> Can I use a "lateral" join of some kind, or somehow tell PostgreSQL
> to not be so optimized?


You can populate the table with a single statement:

with people (name) as (
   values ('Frank'), ('Joe'), ('Steve'), ('Earl')
)
insert into secretsanta (giver, recipient)
select distinct on (n1.name) n1.name, n2.name
from people n1
   join people n2 on n1.name <> n2.name
order by n1.name;

On Tue, Dec 22, 2015 at 9:49 PM, Lou Duchez <lou@paprikash.com> wrote:
> I have a company with four employees who participate in a Secret Santa
> program, where each buys a gift for an employee chosen at random.  (For now,
> I do not mind if an employee ends up buying a gift for himself.)  How can I
> make this work with an SQL statement?
>
> Here is my Secret Santa table:
>
> --
> create table secretsanta
> (giver text,
> recipient text,
> primary key (giver));
>
> insert into secretsanta (giver) values ('Frank'), ('Joe'), ('Steve'),
> ('Earl');
> --

with
  g as (select giver, row_number() over () as rownum from secretsanta),
  r as (select giver, row_number() over () as rownum from (select
giver from secretsanta order by random()) as x)
update secretsanta
  set recipient = r.giver
  from g join r on g.rownum = r.rownum
  where secretsanta.giver = g.giver;

--
Kevin Grittner
EDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

> Why not generate the required results in a SELECT then update from
> that. row_number() could allow you to generate a random number to each
> giver, then we can generate another random number and join to each
> random number. That'll give you a giver and recipient combination.
>
> e.g:
>
> select giver,recipient from
> (select row_number() over (order by random()) rn, giver from
> secretsanta) g
> inner join
> (select row_number() over (order by random()) rn, giver recipient from
> secretsanta) r on g.rn = r.rn
>
> You can then wrap that up in a CTE, something along the lines of:
>
> with cte (giver, recipient) as (
> select giver,recipient from
> (select row_number() over (order by random()) rn, giver from
> secretsanta) g
> inner join
> (select row_number() over (order by random()) rn, giver recipient from
> secretsanta) r on g.rn = r.rn
> )
> update secretsanta set recipient = cte.recipient from cte WHERE
> cte.giver = secretsanta.giver;
>
Hey, I think that works!  Thanks!