Rails query join on regex

Question

This is a query run very infrequently (as admin), and it's not worth indexing/ using up RAM with sphinx.

So, with the variables set up something like this: fn, ln, em = 'John', 'Smith', '[email protected]'

The query is something like this:

profiles = Profile.joins(:user).where(:users=>{"email" => em}).
  where("first_name LIKE '%#{fn}%' AND last_name LIKE '%#{ln}%'")

Except that I want the email to accept a substring (e.g. if em == 'th@gm', then it should match '[email protected]'). How do you write this to do a join selecting only those users with a variable matching a regex?

Community · Accepted Answer · 2017-05-23 12:20:25Z

-1

(I got my inspiration from here):

Have you tried something like:

profiles = Profile.joins(:user).where("first_name LIKE ? AND last_name LIKE ?", "%#{fn}%", "%#{ln}%")
profiles.select{|p| p.user.email =~ /#{em}/}

edited May 23, 2017 at 12:20

CommunityBot

11 silver badge

answered Oct 7, 2013 at 21:31

David Weiser

5,2034 gold badges30 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

MrTheWalrus Over a year ago

This is likely less efficient than a pure-SQL solution. Whether that's worth worrying about or not depends on how many Profiles there are likely to be that match the where clause but would fail the email test. If it's only a handful, this is probably a good solution. If there are many, it's less so.

David Weiser Over a year ago

Well, if we were aiming at efficiency, it would be better to use a stored procedure. However, since ActiveRecord's top priority isn't efficiency (and this is a Rails-esque question), I figured this is a workable answer.

shaundaley39 Over a year ago

Yes - this is a great solution. It's an essential query, but it is rarely required and not performance critical. There are many users, but it can be handled in memory at present - so a ruby select seems appropriate. Thanks!

David Weiser Over a year ago

@muistooshort: You're right. I was just copying the OP's code. I'll fix that.

shaundaley39 Over a year ago

mu, did a search and ended up looking at a previous answer of yours. Would you consider this safe from SQL injection? profiles = Profile.where("first_name LIKE '%#{fn.gsub(/[!%_]/) { |x| '!' + x }}%' ESCAPE '!' AND last_name LIKE '%#{ln.gsub(/[!%_]/) { |x| '!' + x }}%' ESCAPE '!'")

Collectives™ on Stack Overflow

Rails query join on regex

1 Answer 1

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related