Rails 2.3: How to turn this SQL statement into named_scope

Question

It's a little tricky to figure out how to create 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;

The category must be a variable to change.

What is the most efficient way to write named_scope 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 comment than answer, but it doesn't fit ...

zed_oxff is on the ball.

To simplify things and keep them DRY, you can consider defining discrete namespaces instead of one large one and linking them together.

For instance:

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

So, you will use them as follows:

Person.random_order.limit(3).whatever