How to find records where column is not null or empty in Rails 4 or 5

We all know that especially in legacy applications sometimes our database columns are not that well maintained. So we need to query for, or exclude rows containing nil/NULL and empty strings ("") as well. How can we do it in ActiveRecord?

Let’s say your Active Record model is called User and the DB column we are going to be searching by is category.

find records where column is null or empty

That’s simple.

User.where(category: [nil, ""])

find records where column is not null or empty

Still easy.

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

A contribution from pjmartorell:

User.where.not(category: "") will also find records where column is not null or empty, but in a more efficient way than using the array form (NOT IN). Because of the nature of not, it does not fetch records where category is null. You can read more about this topic here. Checked in Rails 6.0.1 and PostgreSQL.

This not() clause is going to only apply to one where. You are not going to negate all previous conditions. In other words you can safely use it like this:

User.
  where(state: "active").
  where.not(category: [nil, ""]).
  where("created_at > ?", 5.days.ago)

to get SQL statement like this:

SELECT "users".* FROM "users"
WHERE
  "users"."state" = "active" AND
  (NOT (("users"."category" = '' OR "users"."category" IS NULL))) AND
  (created_at > '2017-09-01 14:27:11')

Would you like to continue learning more?

If you enjoyed the article, subscribe to our newsletter so that you are always the first one to get the knowledge that you might find useful in your everyday Rails programmer job.

Content is mostly focused on (but not limited to) Ruby, Rails, Web-development and refactoring big, complex Rails applications.