Query in a string column for one of the value in an array like multiple OR (using full text search)

Question

In a rails 4 app, in one model I have a column containing multiple ids as a string with comma separated values.

"123,4568,12"

I have a "search" engine that I use to retrieve the records with one or many values using the full text search of postgresql I can do something like this which is very useful:

records = MyModel.where("my_models.col_name @@ ?", ["12","234"])

This return all the records that have both 12 and 234 in the targeted column. The array comes from a form with a multiple select.

Now I'm trying to make a query that will find all the records that have either 12 or 234 in there string.

I was hopping to be able to do something like:

records = MyModel.where("my_models.col_name IN (?)", ["12","234"])

But it's not working.

Should I iterate through all the values in the array to build a query with multiple OR ? Is there something more appropriate to do this?

EDIT / TL;DR

@BoraMa answer is a good way to achieve this.

To find all the records containing one or more ids referenced in the request use:

records = MyModel.where("my_models.col_name @@ to_tsquery(?)", ["12","234"].join('|'))

You need the to_tsquery(?) and the join with a single pipe |to do a OR like query.

To find all the records containing exactly all the ids in the query use:

records = MyModel.where("my_models.col_name @@ ?", ["12","234"])

And of course replace ["12","234"] with something like params[:params_from_my_form]

Postgres documentation for full text search

Can you change the schema so that you're not storing CSV in a column? Maybe use a PostgreSQL array column instead. — mu is too short
– mu is too short, Commented Mar 18, 2016 at 5:22
I just see your comment. Actually I can't change the schema but's that's a good idea for the future. For the moment I'll continue using the advantage of full text search. See my update for the solution. — coding addicted
– coding addicted, Commented Mar 18, 2016 at 14:22

Matouš Borák · Accepted Answer · 2016-03-18 13:51:10Z

1

If you already started to use the fulltext search in Postgres in the first place,I'd try to leverage it again. I think you can use a fulltext OR query which can be constructed like this:

records = MyModel.where("my_models.col_name @@ to_tsquery(?)", ["12","234"].join(" | "));

This uses the | operator for ORing fulltext queries in Postgres. I have not tested this and maybe you'll need to do to_tsvector('my_models.col_name') for this to work.

See the documentation for more info.

edited Mar 18, 2016 at 13:51

answered Mar 18, 2016 at 7:25

Matouš Borák

16k2 gold badges46 silver badges55 bronze badges

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

4 Comments

coding addicted Over a year ago

Hi BoraMa, your approach seems very promising I think this could actually works. Let me try your idea and I get back to you! Thanks.

coding addicted Over a year ago

It's working, you lead me on the right path. the tricky thing is that the OR operator for full text is a single | their documentation where not totally clear for my purpose. The to_tsquery is necessary but not the tsvector that is seems to be for grouping col you want to search in. I'll update my question to provide informations for future readers. I suggest you update your answer too. Thanks for your idea.

Matouš Borák Over a year ago

Great that you got it working! I updated the answer based on you findings.

coding addicted Over a year ago

Thanks, it's definitely useful and now I can set search engine for exclusive or inclusive match.

kajal ojha · Accepted Answer · 2016-03-18 05:12:03Z

0

Suppose your ids are :

a="1,2,3,4"

You can simply use:

ModelName.find(a)

This will give you all the record of that model whose id is present in a.

answered Mar 18, 2016 at 5:12

kajal ojha

1,2888 silver badges21 bronze badges

1 Comment

coding addicted Over a year ago

Hi kajal, I think your missing the point. I'm no trying to find the model which ids are referenced in the query. I'm trying to find the model which holds the references of another one (ex: categories ids). Sadly a simple find cannot work in this case. Thanks for taking the time to answer!

Hieu Pham · Accepted Answer · 2016-03-18 05:26:01Z

0

I just think a super simple solution, we just sort the ids in saving callback of MyModel, then the query must be easier:

class MyModel < ActiveRecord::Base
  before_save :sort_ids_in_col_name, if: :col_name_changed?

  private

  def sort_ids_in_col_name
    self.col_name = self.col_name.to_s.split(',').sort.join(',')
  end
end

Then the query will be easy:

ids = ["12","234"]
records = MyModel.where(col_name: ids.sort.join(',')

answered Mar 18, 2016 at 5:26

Hieu Pham

6,6922 gold badges33 silver badges50 bronze badges

1 Comment

coding addicted Over a year ago

Hi Hieu, thanks for your answer but a where(col_name: ids) will be an exact match an I will not be able to find the records that have, for instance, only one of the ids from the query. And to find the records that have all the ids the @@ search is perfect because it's looking for each words (or id in my case) that are separated by a comma. Imagine that I'm trying to find a simple query to avoid to generate a long one with a lot of OR in it (one ORfor each id).

Collectives™ on Stack Overflow

Query in a string column for one of the value in an array like multiple OR (using full text search)

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related