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
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
find records where column is null or empty
User.where(category: [nil, ""])
find records where column is not null or empty
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
null. You can read more about this topic here. Checked in Rails 6.0.1 and PostgreSQL.
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.