ActiveRecord: Add the like or/and ilike methods

zedtux · November 11, 2016, 12:21pm

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_FB · November 11, 2016, 5:44pm

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

Janusz_M · January 14, 2021, 8:32pm

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

jonian · January 14, 2021, 10:44pm

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.

sdubois · January 15, 2021, 7:26am

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

github.com/rails/rails

Where with block

rails:main ← brunvez:where-with-block

opened 01:41AM - 27 May 20 UTC

brunvez

+86 -4

### Summary Motivated by @tenderlove's PR about [exposing `arel_table`](https…://github.com/rails/rails/pull/39198) I implemented a way to query using blocks and which I think provides great power without sacrificing readability. The PR comes in three flavors which are divided into corresponding commits, each one building on the last and adding functionalities. Each commit is backward compatible and comes with tests as documentation of the syntax, I'm happy to do add more tests to cover all paths. The first commit is the vanilla version which sends the block an object that allows using the same syntax as `arel` and adds methods for associations. For a `Post` with many `Comment`s and each `Comment` with an `User` an example would be: ```ruby # simple query Post.where { |post| post[:updated_at].gt(1.day.ago) } # deeply nested Post.joins(comments: :user).where { |post| post.comments.user[:first_name].eq("John") } ``` The second commit defines a method for each attribute, which I think adds better readability. Redoing the examples with methods it'd be: ```ruby # simple query Post.where { |post| post.updated_at.gt(1.day.ago) } # deeply nested Post.joins(comments: :user).where { |post| post.comments.user.first_name.eq("John") } ``` The last commit executes the block in the instance of the `QueryComposer` (open to other names) which allows call methods directly without the need to use the parameter from the block, which is still passed so both syntaxes are allowed. Once again an example with the same domain: ```ruby # simple query Post.where { updated_at.gt(1.day.ago) } # deeply nested Post.joins(comments: :user).where { comments.user.first_name.eq("John") } ``` Any comments and feedback are welcome, I'm happy to add any requested changes @kamipo @rafaelfranca @flanger001. The only issue I found while running the tests was that `Model.unscoped.where(...) { }` associated the block to `unscoped` and so I had to change the tests to use `scoping` after the `where` call. I think having the block not associating to the last call is also a bit confusing IMHO and so I'd rather have this functionality than the ability to do that. Domain for reference: ```ruby class Post < ApplicationRecord has_many :comments end class Comments < ApplicationRecord belongs_to :user end class User < ApplicationRecord end ``` ### Other Information Again, I'm looking for feedback on the syntaxes but I'm happy to provide benchmarks or anything else that might be interesting. I'm also aware that there are changes that may improve performance, like defining the attributes method lazily and not creating a new object every time an association is queried, and I'd be more than willing to test alternatives to the current implementation. I did a benchmark just to check this does not slow down query creation too much and it turns out it's actually faster this way. Though I wouldn't call it a noticeable improvement. ``` Warming up -------------------------------------- with_hash 771.000 i/100ms Calculating ------------------------------------- with_hash 7.716k (± 4.2%) i/s - 38.550k in 5.004979s Warming up -------------------------------------- with_block 1.376k i/100ms Calculating ------------------------------------- with_block 15.276k (±17.2%) i/s - 74.304k in 5.081121s Pausing here -- run Ruby again to measure the next benchmark... Comparison: with_block: 15276.3 i/s with_hash: 7716.1 i/s - 1.98x slower ``` Here's the benchmark script: ```ruby # frozen_string_literal: true require 'bundler/inline' gemfile(true) do source 'https://rubygems.org' git_source(:github) { |repo| "https://github.com/#{repo}.git" } gem 'rails', github: 'brunvez/rails', branch: 'where-with-block' gem 'benchmark-ips', '< 2.8' gem 'byebug' gem 'sqlite3' end require 'active_record' require 'active_model' ActiveRecord::Base.establish_connection adapter: 'sqlite3', database: ':memory:' @connection = ActiveRecord::Base.connection @connection.create_table :posts, force: true do |t| t.string :title end @connection.create_table :users, force: true do |t| t.string :name end @connection.create_table :comments, force: true do |t| t.string :content t.belongs_to :post t.belongs_to :user end class Post < ActiveRecord::Base has_many :comments end class Comment < ActiveRecord::Base belongs_to :post belongs_to :author, class_name: 'User', foreign_key: :user_id end class User < ActiveRecord::Base has_many :comments end Benchmark.ips do |x| x.report('with_hash') do Post.joins(comments: :author) .where(title: 'Something', comments: { content: 'Cool' }, users: { name: 'John', id: 10 } ) end x.report('with_block') do Post.joins(comments: :author).where do |post| post.title.eq('Something') .and(post.comments.content.eq('Cool')) .and(post.comments.author.name.eq('John')) .and(post.comments.author.id.eq(10)) end end x.report('with_multiple_hashes') do Post.joins(comments: :author) .where(title: 'Something') .where(comments: { content: 'Cool' }) .where(users: { name: 'John', id: 10 }) end x.report('with_multiple_blocks') do Post.joins(comments: :author) .where { |post| post.title.eq('Something') } .where { |post| post.comments.content.eq('Cool') } .where { |post| post.comments.author.name.eq('John').and(post.comments.author.id.eq(10)) } end x.hold! 'temp_results' x.compare! end ```

malomalo · January 15, 2021, 11:44pm

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.

formigarafa · July 18, 2023, 11:21am

With help of ApplicationRecord being a base class for all models I made a simple solution that covers most of it.

Added this method to Application model to allow me to choose the operation while building the where:

class ApplicationRecord < ActiveRecord::Base
  def self.where_op(op, params)
    params.reduce(all) do |scope, (column, value)|
      scope.where(scope.arel_table[column].send(op, value))
    end
  end
end

Then all arel operations are available, example:

users = User.where_op(:matches, email: "%@gmail.com", name: "%smith%")
users = users.where_op(:between, created_at: 2.days.ago..)

# => SELECT "users".* FROM "users" WHERE "users"."email" ILIKE '@gmail.com' and "users"."name" ILIKE '%smith%' AND "users"."created_at" >= '2023-07-16 11:03:34.494592'

Note that the :between above was just a very forced example because active record would give same result using where(created_at: 2.days.ago..).

This supports as operation: :between, :eq, :eq_all, :gt, :gteq, :in, :lt, :lteq, :matches and others.

Walther · September 28, 2023, 8:12pm

I’ll give this a try - been longing for LIKE like for ever Arel get’s a bit long in the tooth after a while but this ‘sugar’ is pretty sweet

Topic		Replies	Views
[ActiveRecord] `where.or` query method rubyonrails-core feature	6	2962	October 16, 2019
ActiveRecord find rubyonrails-talk	6	115	March 29, 2007
Add `ActiveRecord::Base#[]` rubyonrails-core feature	6	680	September 8, 2021
ActiveRecord English Like Queries v0.0.2 rubyonrails-talk announcement	0	168	August 19, 2008
Extending raw queries with optional where clause rubyonrails-talk	4	508	November 16, 2023

ActiveRecord: Add the like or/and ilike methods

Related topics

More Resources