ActiveRecord: Add the like or/and ilike methods

[zedtux]

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 :)

[Jason Fleetwood-Boldt]

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

----

Jason Fleetwood-Boldt

te...@datatravels.com

http://www.jasonfleetwoodboldt.com/writing

If you'd like to reply by encrypted email you can find my public key on jasonfleetwoodboldt.com (more about setting GPG: https://gpgtools.org)