CODEWITHSUNDEEP
ruby-on-railsregexactiverecord
Zack
Zack

Reputation: 2497

How to perform an active record search based on a regular expression

I have a standard User model in my rails app. Complete with First Name, Last Name, Email, etc. I am attempting to search for all Users that meet a certain criteria.

I know that I can search for a specific email address as follows:

User.find_by_email('[email protected]')

But say I want to find all users that have a Gmail address (for example). Ideally this would be done via:

User.find_by_email(/.*@gmail.com/)

But that doesn't work. It gives me a TypeError: Cannot visit Regexp. How can I perform this activerecord search?

Upvotes: 10

Views: 12061

Answers (3)

tanius
tanius

Reputation: 16769

In your specific case, you are looking for a string ending in a known substring: .*@gmail.com

You don't need regexps for that. A LIKE search is sufficient and faster:

User.where("email LIKE ?", '%@gmail.com')

Upvotes: 5

Masa Sakano
Masa Sakano

Reputation: 2267

As of PostgreSQL Ver.14, @davegson's answer does not work with Postgres. Here are two ways – for case-insensitive matching, as email addresses should be:

User.where("email ~* ?", '^.*@gmail.com$')
  # "~" for case-sensitive match
User.where("regexp_match(email, ?, ?) IS NOT NULL", '.*@gmail.com', 'i')

# Wrong way
User.where("regexp_matches(email, ?)", '.*@gmail.com')
 # => ERROR:  set-returning functions are not allowed in WHERE (PG::FeatureNotSupported)

PostgreSQL official docs

Upvotes: 3

davegson
davegson

Reputation: 8331

.find(_by) only returns the first match. Since you want to find all users with a Gmail address you need to use .where.

You can easily combine the query with some regex:

With Mysql, use REGEXP

User.where("email REGEXP ?", '.*@gmail.com')

With Postgres, use regexp_matches:

User.where("regexp_matches(email, ?)", '.*@gmail.com')

Upvotes: 18

Related Questions