Rails scope for IS NOT NULL and is not empty/blank

问题:

I have the following scope:

scope :comments, :conditions => ['text_value IS NOT NULL']

But I also want the conditions to say "OR text_value IS NOT EMPTY" (or something to that effect).

I don't want to select any rows where text_value is empty/blank.

回答1:

As Erwin points out, a simple text_value <> '' comparison will work in this case.

scope :comments, where("text_value <> ''")

(Rails 3 prefers this query syntax for scope—as well as find, all, etc.—rather than an options hash e.g. :conditions => .... The latter is deprecated in Rails 3.1.)

In Rails 4, the second argument should be a lambda instead:

scope :comments, ->{ where("text_value <> ''") }

回答2:

In Rails 4 you can do

where.not(text_value: '')

回答3:

rails 4

scope :comments, -> { where.not(:text_value => nil) }

回答4:

Use text_value <> '' to efficiently cover both cases.

Will only be TRUE for a text_value that is neither NULL nor empty.

回答5:

scope :comments, where("text_value <> ''")

回答6:

Personally I am doing like this:

1) Add to initializers

class Arel::Attributes::Attribute
  # Encode column name like: `posts`.`author_id`
  def to_sql
    "`#{relation.table_name}`.`#{name}`"
  end

  def is_not_empty
    "#{to_sql} <> ''"
  end
end

2) Add to your model

scope :comments, -> { where(arel_table[:text_value].is_not_empty) }

Good luck!