CODEWITHSUNDEEP
ruby-on-railspostgresql
coding addicted
coding addicted

Reputation: 3430

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

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

Upvotes: 0

Views: 722

Answers (3)

Matouš Borák
Matouš Borák

Reputation: 15944

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.

Upvotes: 1

Hieu Pham
Hieu Pham

Reputation: 6707

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(',')

Upvotes: 0

kajal ojha
kajal ojha

Reputation: 1278

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.

Upvotes: 0

Related Questions