Multiple LIKE and AND operators in RAILS/POSTGRESQL

Question

I'm using Rails 5.0.1 and Postgresql as my database. I have a table with column :content which contains words.

The problem: When I'm looking for a specific word, I want to see if the word contains letters (chars) of my choice. Let's say i want to DB to return words containg letters "a", "b" and "c" (all of them, but with no specific order)

What I'm doing: I found that i could use Word.where("content like ?", "%a%").where("content like ?", "%b%").where("content like ?", "%c%") OR Word.where("content like ? content like ? content like ?", "%a%", "%b%", "%c%") In both cases even if i switch order of given letters/substrings it works fine, ex. both would find word "back", "cab" etc..

The question: Is there any better/more DRY way to do it? What if want to find word with 8 different letters? Do i have to use "content like ?" 8 times? Is it possible to pass arguments as an array? (let's assume i don't know how many letters user will input)

score 2 · Answer 1 · answered Feb 08 '17 at 22:43

2

I found a solution:

letters = ["%a%", "%b%", "%c%"]
Word.where((['content LIKE ?'] * letters.size).join(' AND '), *letters)

This is easy and much better than I was using.

answered Feb 08 '17 at 22:43

Tomasz Bubała

2,093
1
11
18

score 2 · Accepted Answer · answered Feb 08 '17 at 22:44

2

PostgreSQL has a handy expr op all (array) expression so you can say things like:

where content like all (array['%a%', '%b%', '%c'])

as a short form of:

where content like '%a%'
  and content like '%b%'
  and content like '%c%'

Also, ActiveRecord will conveniently replace a ? placeholder with a comma-delimited list if you hand it a Ruby array. That lets you say things like:

Word.where('content like all (array[?])', %w[a b c].map { |c| "%#{c}%" })

and:

Word.where('content like all (array[?])', some_other_array.map { |c| "%#{c}%" })

answered Feb 08 '17 at 22:44

mu is too short

426,620
70
833
800

thanks for great answer! i also tried to use SIMILAR TO but it was incredibly slow. This is exactly what i was looking for – Tomasz Bubała Feb 08 '17 at 22:50
1

There are also some [indexing options](http://stackoverflow.com/a/13452528/479863) that work with LIKE. – mu is too short Feb 08 '17 at 23:00

score 0 · Answer 3 · answered Feb 08 '17 at 21:49

0

I think the SIMILAR TO operator might help. It allows you to pass in a regular expression that you could construct on the fly.

letters = ['a', 'b', 'c']
pattern = "%(#{letters.join('|')})%"
Word.where("content SIMILAR TO ?", pattern)

answered Feb 08 '17 at 21:49

Brian

5,300
2
26
32

This pattern doesn't use all of the letters. It uses one, or two, or all of them, or first and fifth etc. I want to use all of given letters, but not in any particular order – Tomasz Bubała Feb 08 '17 at 21:56

Multiple LIKE and AND operators in RAILS/POSTGRESQL

3 Answers3

Linked