Ancinek Ancinek - 2 months ago 19
MySQL Question

Rails advanced search query

I have a database with Lab models. I want to be able to search them using multiple different methods.


  1. I chose to use one input field and separate query into words array:

    search = search.split(/[^[[:word:]]]+/).map{|val| val.downcase}

  2. I use Acts-as-taggable gem so it would be nice to include those tags in search to:

    tag_results = self.tagged_with(search, any: true, wild: true)

  3. For methods down below it seemed to be necessary to use:

    search = search.map{|val| "%#{val}%"}

  4. Sunspot seemed also a great way to go for full-text search so

    full_text_search = self.search {fulltext search}
    full_text_results = full_text_search.results

  5. I decided to go also with simple database query searching for a Lab name:

    name_results = self.where("LOWER(name) ILIKE ANY ( array[?] )", search)

  6. Lastly I need all of the results in one array so:

    result = (tag_results + name_results + full_text_results).uniq



It works perfectly (what I mean is that the result is what I expect) but it returns a simple array and not
ActiveRecord::Relation
so there is no way for me to use method like
.select()
or
.order()
on the results.

I want to ask is there is some better way to implement such search? I was searching for search engines but it seems like there is nothing that would fit my idea.

If there is not - is there a way to convert an array into ActiveRecord::Relation? (SO says there is no way)

Answer

Answering this one:

is there a way to convert an array into ActiveRecord::Relation? (SO says there is no way)

You can convert an array ob ActiveRecord objects into ActiveRecord::Relation by fetching ids from array and querying your AR model for objects with these ids:

Model.where(id: result.map(&:ids)) # returns AR, as expected.

It is the only way I am aware of.