ActiveRecord and getting unused records from a relationship, find_by_sql or named_scope?

Question

Some context:

I have some models that look like this

Actor has_many Acts

Act belongs_to Actor, belongs_to Decision

Decision has_many Acts, belongs_to Prompt

Prompt has_many Decisions

What I need to do is in the ActorsController, get a random Prompt that has not been used yet of all the available Prompts.

In my rails app, Actors are presented with prompts that give them a few choices to make. When they make a choice (Decision), that is saved in the db as an Act.

I've tried various iterations of named_scope and find_by_sql, but none worked, and I'm not even sure if my thinking was right to begin with on them, since there are so many models at work, and I don't seem to know where to start.

I hope this gives an idea of what I'm up against. I'd appreciate even a general pointer to form a plan of attack even.

Thanks!

edit

After chewing on this for a couple hours, I've got something working but it's very messy, and my logs are filled with SQL calls, so it could definitely stand a critical eye.

In the Prompt model:

  named_scope :available, lambda { |used| 
    { :conditions => ["id NOT IN (?)", used ] }
  }

In the Actor model:

  def used_prompts
    prompts = Array.new
    if self.acts && self.acts.length >= 1
      self.acts self.acts.each { |act| prompts.insert(0, act.decision.prompt.id) }
      return prompts.sort
    else
      return [0]
    end
  end

And in the ActorsController:

@prompt = Prompt.available(@actor.used_prompts).find(:first, :order => "RAND()")

Obviously the if block in used_prompts is one guilty party here, but I don't know a better way to deal with that since I can't do self.acts.decisions.each or some such thing. Maybe someone can school me :)

Answer 1

The simplest thing to do would be to add a after_create or similar callback on the Decision model that marks the associated prompt as used. You could also achieve this using some joins, but that would take a little more work, and will possibly lead to scalability issues (if you care).