Rails 2.3: How to turn this SQL statement into a named_scope

Question

Having a bit of difficulty figuring out how to create a named_scope from this SQL query:

select * from foo where id NOT IN (select foo_id from bar) AND foo.category = ? ORDER BY RAND() LIMIT 1;

Category should be variable to change.

What's the most efficient way the named_scope can be written for the problem above?

Answer 1

  named_scope :scope_name, lambda { |category|
    { 
      :conditions => ["id NOT IN (select foo_id from bar) AND foo.category = ?", category],
      :order => 'RAND()',
      :limit => 1
    }
  }

Answer 2

More of a comment than an answer but it won't really fit...

zed_oxff is on the ball.

To simplify things and keep them DRY, you might consider defining discrete named scopes instead of one big one, and chaining them together.

For example:

named_scope :random_order, :order => 'RAND()'
named_scope :limit, :lambda => { |limit| :limit => limit }
named_scope :whatever, ...

So you would use them as follows:

Person.random_order.limit(3).whatever