named_scope -- Finding entries where the field is neither null nor empty/blank

Question

I only want to find the records that aren't null or empty/blank, currently I have;

named_scope :in_gallery, :conditions => ["gallery IS NOT NULL"]

(gallery is a string) but if the user inputs then deletes a title then this empty string is included in the results.

to clarify I want to be able to select only the entries where the field has some text...

Answer 1

What about

named_scope :in_gallery, :conditions => ["gallery IS NOT NULL AND gallery != ''"]

?

Answer 2

I think J's named scope definition looks right, but beyond that, I'd question why you are using both NULL and the empty string to mean "not in a gallery". If you have control over all the inserts and updates to this database, I'd recommend picking one or the other (NULL seems more natural to me) and sticking with it. It'll simplify your code in places like, for example, this named scope, and allowing both can lead to some really annoying bugs if you forget to check one or the other.

A simple way to implement this would be to add a validation to disallow setting the gallery to the empty string, and then fix whatever breaks (hopefully you have tests). If you're exposing an API and need to allow empty galleries for backwards compatibility, you could set a before_save hook to change the empty string to nil.