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

Question

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?

Answer 1

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

Answer 2

In your case /.*@gmail.com/ of looking for a string ending in a known substring, you don't actually need true regexps. Instead, a faster alternative is a LIKE search:

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