2

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;

?

Mike Woodhouse
  • 51,832
  • 12
  • 88
  • 127
John Bachir
  • 22,495
  • 29
  • 154
  • 227

4 Answers4

3

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

Oinak
  • 1,805
  • 1
  • 12
  • 13
2
Widget.find(:all, :conditions => "color IS NOT NULL")
Francisco Soto
  • 10,277
  • 2
  • 37
  • 46
1

Not quite as elegant, but this should work:

Widget.find(:all, :conditions => "'color' IS NOT NULL")
harald
  • 5,976
  • 1
  • 24
  • 41
1

Try this:

Widget.all(:conditions => "color IS NOT NULL")
Harish Shetty
  • 64,083
  • 21
  • 152
  • 198
  • 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 Bachir Jun 30 '10 at 21:00