activerecord not like query

Question

I could not find an activerecord equivalent of "Not Like". I was able to find a where.not, but that will check if a string does not match a value, as so:

User.where.not(name: 'Gabe')

is the same as:

User.where('name != ?', 'Gabe')

I was looking for a NOT LIKE, where the value is not contained in the string. The equivalent sql query would look like as follows:

SELECT * FROM users WHERE name NOT LIKE '%Gabe%'

In ActiveRecord I can currently get away with the following:

User.where("name NOT LIKE ?", "%Gabe%")

But that leaves a lot to be desired. Any new additions to Rails 4 to facilitate this?

Answer 1

As others have pointed out ActiveRecord does not have a nice syntax for building like statements. I would suggest using Arel as it makes the query less database platform specific (will use ilike for sqlite & like for other platforms).

User.where(User.arel_table[:name].does_not_match('%Gabe%'))

You could also implement this as a scope to contain the implementation to the model:

class User < ActiveRecord::Base
  scope :not_matching, 
    -> (str) { where(arel_table[:name].does_not_match("%#{str}%")) }
end

Answer 2

Well, you can do something like:

User.where.not("name LIKE ?", "%Gabe%")

Note: This is only available in Rails 4.

Answer 3

Unfortunately ActiveRecord does not have a like query builder. I agree that the raw 'NOT LIKE' leaves a lot to be desired; you could make it a scope (scope :not_like, (column, val) -> { ... }), but AR itself does not do this.

Answer 4

Just addition to the answer of "where.not" of active record. "where.not" will exclude null values also. i.e. Query User.where.not(name: 'Gabe') will leave record with name 'Gabe' but it also exclude name column with NULL values. So in this scenario the solution would be

User.where.not(name: 'Gabe') .or(User.where(name: nil))