Logic and code help

Question

Here are my models and associations:

• User has many Awards
• Award belongs to User
• Prize has many Awards
• Award belongs to Prize

Let's pretend that there are four Prizes (captured as records):

• Pony
• Toy
• Gum
• AwesomeStatus

Every day a User can be awarded one or more of these Prizes. But the User can only receive each Prize once per day. If the User wins AwesomeStatus, for ex, a record is added to the Awards table with a fk to User and Prize. Obviously, if the User doesn't win the AwesomeStatus for the day, no record is added.

At the end of the day (before midnight, let's say), I want to return a list of Users who lost their AwesomeStatus. (Of course, to lose your AwesomeStatus, you had to have the day before.) Unfortunately, in my case, I don't think observers will work and will have to rely on a script. Regardless, how would you go about determining which Users lost their AwesomeStatus? Note: don't make your solution overly dependent on the period of time -- in this case a day. I want to maintain flexibility in how many times per whatever period Users have an opportunity to win the prize (and to also lose it).

Answer 1

I would probably do something like this:

The class Award should also have a column awarded_at which contains the day the prize was awarded. So when it is time to create the award it can be done like this:

# This will make sure that no award will be created if it already exists for the current date
@user.awards.find_or_create_by_prize_id_and_awarded_at(@prize.id, Time.now.strftime("%Y-%m-%d"))

And then we can have a scope to load all users with an award that will expire today and no active awards for the supplied prize.

# user.rb
scope :are_losing_award, lambda { |prize_id, expires_after| 
  joins("INNER JOIN awards AS expired_awards ON users.id = expired_awards.user_id AND expired_awards.awarded_at = '#{(Time.now - expires_after.days).strftime("%Y-%m-%d")}'
         LEFT OUTER JOIN awards AS active_awards ON users.id = active_awards.user_id AND active_awards.awarded_at > '(Time.now - expires_after.days).strftime("%Y-%m-%d")}' AND active_awards.prize_id = #{prize_id}").
  where("expired_awards.prize_id = ? AND active_awards.id IS NULL", prize_id)
}

So then we can call it like this:

# Give me all users who got the prize three days ago and has not gotten it again since
User.are_losing_award(@prize.id, 3)

There might be some ways to write the scope better with ARel queries or something, I'm no expert with that yet, but this way should work until then :)