join query in rails 4

Question

i am developing a job portal using rails 4. i have search box where job seeker can search jobs. currently i am able to do it for job title and description , as follows

scope :by_name_or_desc,lambda{|search| where(" title ilike (?) or description ilike(?)","%#{search}%","%#{search}%") if search.present?}

i want to use the same search box for skills also where seeker can search job using skill. i have skill_ids as a column in my Job model . which is array field. how can i modify above query to do the same.

i also tried doing this ,

scope :by_name_or_desc,lambda{|search| where(" title ilike (?) or description ilike(?) or '#{search}' = ANY (skill_ids)","%#{search}%","%#{search}%") if search.present?}

Answer 1

If you use PostgreSQL, you should take a look at Full Text Search feature. Otherwise i recommend you to use Elastic Search or Sphinx for this kind of stuff. Full-text search is not what RDBMS designed for. With project's growth your approach will be only a headache.