ActiveRecord: Add the like or/and ilike methods

It’s really nice to be able to write code without type a single line of SQL (well as much as possible) and let the framework taking care of it.
I like to do User.where(active: true, deleted_at: nil, country_id: Country.luxembourg).

But in the case I would like to find using a LIKE or ILIKE, it is not possible to write User.where(active: true).ilike(email: email).

Is there any reason why this has not been done ? And could it be done ?

Thank you :slight_smile:

1 Like

My understanding is that the AR Query syntax is intended to be database-agnostic.

For these you are supposed to do something akin to this:

where('name LIKE ?', "%#{pattern}%")

The reason it’s not first-class in Rails is because the Rails core doesn’t want to write SQL-dependent APIs that would work, say, for PostgreSQL but not MySQL.

Here’s a SO post about how to turn that into a class-level method

http://stackoverflow.com/questions/23207644/search-using-like-query-in-ruby-on-rails

One of the inherent problems in the design proposal of yours is that the where() clause, by necessity, needs to know about all of its operators so it can construct the correct clause in SQL. As well, you will note of course that these return A-Rel objects, which confuses many new developers, which is what allows them to be chained. However, you can’t actually do this:

Person.where(:name => "John").where(:lastname => "Smith")

Because the 2nd where clause would need to re-interpret the first where clause (as well, it's totally ambiguous if this should be OR or AND)

So you see it is sort of an inherent problem.

However, I wouldn't mind if a future version of Rails could consider the LIKE query a first-class thing in AR, doing something such as:

User.where(active: true, email_like: email)

In my made-up example above, AR would need to be smart enough to recognize the “_like” at the end of the field name and do an email LIKE query instead of an equals query. As well, this would preclude any fields from being named with _like at their end, which could cause design constraints, etc.

-Jason

1 Like

After all those years, I think we really deserve LIKE / ILIKE in ActiveRecord. At the moment we have a plugin available that presents a nice to use implementation of this: GitHub - ReneB/activerecord-like: An Active Record Plugin that allows chaining a more DSL-style 'like' or 'not-like' query to an ActiveRecord::Base#where. Requires Activerecord 5 or higher.

This was also nicely done in Python’s SQLAlchemy, and should work with all sql dialects, although there queries are built in a slightly different way, i.e. operators are methods defined on columns: where(addresses.c.email_address.like('%@msn.com'))

I use GitHub - marcinruszkiewicz/where_chain: WhereChain - don't write Post.where('comments > ?', 5), write Post.where.gt(comments: 5) instead! and it works very well. I also think that this should be added on rails by default.

Also see proposal for “where with block”, which would enable the ilike operator:

We’ve ended up with a where-ish approach in our application by implementing a filter method to support more relation traversal and other types of columns and function on columns.

It works very well for us and give us a lot of functionality.