Find records where an attribute is present

Question

I have a User model with the attributes username, email and name.

username and email are required upon signup, but not name.

What would be the query to find all users that have filled out name (i.e. it is no nil)?

The query should be at least Rails 3.2 and 4.0 compatible.

I'm thinking something in the lines of:

User.where(name: present?)

Answer 1

[UPDATED 13/5/2022]

To get all records where an attribute is not present in SQL, we would write

WHERE attr IS NULL or attr = ''

an easy mistake to make is then to negate this and write

WHERE attr is not null and attr != '' 

However in SQL this equates to writing

WHERE attr != '' 

since the NULL value is always ignored when using the equality operator.

So this translates to rails as follows:

User.where.not(name: '')

[MY OLD ANSWER]

An empty value in a database gets a special value NULL. Testing whether is set uses the special comparator IS NULL or IS NOT NULL.

Then there still remains the possibility that an empty string was filled in, so a complete test would be

@users = User.where("name is NOT NULL and name != ''")

[UPDATED for rails 4+]

Since rails 4 we can write:

User.where.not(name: [nil, ""])

which will generate the same query. Awesome :)

Answer 2

present?

present? is essentially not nil and not empty?:

class Object
  def present?
    !blank?
  end
  def blank?
    respond_to?(:empty?) ? !!empty? : !self
  end
end

ActiveRecord condition

In Rails 4, not conditions can be done without raw sql code.

# Both lines lead to the same result:
User.where.not(name: [nil, ""])
User.where.not(name: nil).where.not(name: "")

Since there is no raw sql code, you don't have to worry about if they work with every database adapter. In fact, this works fine for both, mysql and postgres.

to_sql

You can see how they translate to sql queries if you append .to_sql, for example in the rails console.

# rails console

User.where.not(name: [nil, ""]).to_sql
# => "SELECT \"users\".* FROM \"users\" WHERE (NOT ((\"users\".\"name\" = '' OR \"users\".\"name\" IS NULL)))"

User.where.not(name: nil).where.not(name: "").to_sql
# => "SELECT \"users\".* FROM \"users\" WHERE (\"users\".\"name\" IS NOT NULL) AND (\"users\".\"name\" != '')"

Further Reading

• [1] Rails 4 guide "ActiveRecord Query Interface"
• [2] Definition of present? on github

Answer 3

NOT SQL queries can be built by where.not

@users = User.where.not(name: nil)

Answer 4

Try this:

User.where("name IS NOT NULL AND name != ?", "")

I edited my answer as per @nathavanda comments, which his answer in my opinion should be the accepted one.