Ruby on Rails Active Record - Search with multiple arguments using IN condition

Question

My data structure looks like:

User.first.department
=> "devops maintaining staff"

User.second.department
=> "facility staff"

I'm searching for a search query which does a search with single or multiple arguments for example:

search with single argument

"staff"                # which should return both users

search with multiple arguments

"maintaining devops"   # which should return first user

search with single argument

"maintaining"          # which should return first user

another search with single argument

"facility"             # which should return second user

Is there any useful example how to accomplish this?

Are you looking for exact match or fuzzy? That's going to really change the answer for your question. — Gavin Miller, Jan 29 '17 at 21:30
Have you considered redesigning your database tables structure? Seems like you are embedding a N:M relationship in a single string field. There could be some reasons to do this, but in general it is discouraged (it causes issues like this one) — Bustikiller, Jan 29 '17 at 21:57

score 2 · Accepted Answer · edited May 23 '17 at 12:01

Basically what you're asking for sounds like substring matching. My answer is a "brute force" style of querying and it will become brittle / fall apart really quickly in real-world usage:

# keywords is an array of strings
def keyword_search(keywords)
  query = User

  keywords.each do |keyword|
    query = query.where('department like ?', "%#{keyword}%")
  end

  query.to_a
end

So for your examples you'd get the following queries:

# Returns both users for "staff" 
SELECT "users".* FROM "users" WHERE (department like '%staff%')

# Returns first user for "maintaining devops"
SELECT "users".* FROM "users" WHERE (department like '%maintaining%') AND (department like '%devops%')

# Returns first user for "maintaining devops"
SELECT "users".* FROM "users" WHERE (department like '%maintaining%') 

# Returns first users for "facility"
SELECT "users".* FROM "users" WHERE (department like '%facility%')

Notice for this type of solution, it's an AND query, so all the keywords will have to match to get a result. The % sign in the query makes the query slightly fuzzier, so for %staff% you'd get matches for the following:

staff
staffroom
flagstaffs
overstaff

Which you can adjust by dropping the %s if you're looking for more exact matches.

As Eric suggested, anything more complicated than this is going to require a more advanced solution.

Personally I've used Solr/Websolr and have had good success with it, but the query and search tuning takes a bit of work to understand and implement.

Is it possible to make a search query which matches for all these 4 examples? — Stef Hej, Jan 29 '17 at 22:07
@StefHej It does. Did you try the code out? How'd you test that it's not working? — Gavin Miller, Jan 29 '17 at 22:57
yes it *connects* each keywords with `AND`. Is there a way to do this with `OR`? — Stef Hej, Jan 30 '17 at 00:12
@StefHej your question does have any cases that requires an `OR` predicate. The above code could be modified to use `OR` clauses with rails' `or` statement: http://stackoverflow.com/a/32753177/33226 — Gavin Miller, Jan 30 '17 at 04:30

score 1 · Answer 2 · edited May 23 '17 at 12:24

1

It looks like you need full-text search, which is database dependent.

Here's a related question. For anything more advanced, you might want to look at sphinx and thinking-sphinx.

edited May 23 '17 at 12:24

Community

1
1

answered Jan 29 '17 at 21:23

Eric Duminil

52,989
9
71
124

Ruby on Rails Active Record - Search with multiple arguments using IN condition

2 Answers2