views:

34

answers:

4

I very often want to use dynamic finders to specify NOT NULL. So…

this works:

Widget.find_all_by_color('blue')

this works:

Widget.find_all_by_color(nil)

But how can I do

SELECT * FROM `widgets` WHERE `color` IS NOT NULL;

?

+1  A: 

Not quite as elegant, but this should work:

Widget.find(:all, :conditions => "'color' IS NOT NULL")
harald
+2  A: 
Widget.find(:all, :conditions => "color IS NOT NULL")
Francisco Soto
+1  A: 

Try this:

Widget.all(:conditions => "color IS NOT NULL")
KandadaBoggu
Yep, I knew all those, thanks guys. I guess there is no way to do it with a dynamic finder. KandadaBoggu gets the answer because his is 5 characters shorter :-D
John
+2  A: 

Two ways depending on how specific you want to be:

# in your model
class Widget < ActiveRecord::Base
  # first way
  named_scope :coloured, {:conditions => ["color IS NOT NULL"]}
  # second way
  named_scope :not_null, lambda{|*args| (field=args.first ? {:conditions => ["#{field} is not null",field]} : {}) } }
end

# in your controller
@coloured_widgets = Widget.coloured.all           # using first way
@coloured_widgets = Widget.not_null(:colour).all  # using second way

I hope it helps.

Cheers

Fer