How to find records where column is not null or empty in Rails 4 or 5
source link: https://blog.arkency.com/rails-how-to-find-records-where-column-is-not-null-or-empty/
Go to the source link to view the article. You can view the picture content, updated content and better typesetting reading experience. If the link is broken, please click the button below to view the snapshot at that time.
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, ""])
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.
Recommend
About Joyk
Aggregate valuable and interesting links.
Joyk means Joy of geeK