Q:

Order by relevancy in rails

Here's what I do for filtering items in the search page:
@experiences = Mission.where(active: true)
@experiences = @experiences.where(accept_families: true) if filter_params['forFamily'].present? && filter_params['forFamily'] == true
@experiences = @experiences.includes(:country_missions).where(:country_missions => { :country_id => filter_params['countries'] }) if filter_params['countries'].present?
@experiences = @experiences.includes(:skill_all).where(:skill_missions => { :skill_id => filter_params['skills'] }) if filter_params['skills'].present?

months = []
months.push(12, 1, 2) if filter_params['start_winter'].present? && filter_params['start_winter'] == true
@experiences = @experiences.joins(:start_dates).includes(:start_dates).where('extract(month from start_dates.start) IN (?)', months) unless months.blank?

I want to not filter those items, but instead, sort it by relevancy, some kind of a scoring metric; the more coincidences with the selected tags, strings etc – the higher the score and higher the mission is displayed.
I'm thinking into this kind of an query:
SELECT title, company, user, CASE 
  WHEN upper(company_name) LIKE '%#{word[0].upcase}%' THEN 3
  WHEN upper(company_name) LIKE '%#{company_name.upcase}%' THEN 2
  ELSE 0 END as score      
  FROM selected_tables
  WHERE company_name LIKE '%#{company_name}%'
  ORDER BY score DESC;

But that's kinda too simple; I can't figure how to get a "while" case for each of the tags: each filter_params['countries'], for example, contains an array of IDs: [212,30,1].
That's like, for each country, that exists in the model item, score is incremented by 1.
How do I do this?
I am using Postgres for database.
I already tried something like
SELECT *,
    CASE
      WHEN true THEN 0
    END as score,
    CASE 
      WHEN "skill_missions"."skill_id" IN (1) THEN score = score + 1
    END,
    CASE 
      WHEN "skill_missions"."skill_id" IN (2) THEN score = score + 1
    END

    FROM missions
    LEFT OUTER JOIN "skill_missions" ON "skill_missions"."mission_id" = "missions"."id"

But that isn't correct.

A:

If I understand correctly you looking for something like this:
SELECT title, company, user,
       CASE WHEN upper(company_name) LIKE '%#{word[0].upcase}%' THEN 3 ELSE 0 END
     + CASE WHEN upper(company_name) LIKE '%#{company_name.upcase}%' THEN 2 ELSE 0 END
      as score      
  FROM selected_tables
 WHERE company_name LIKE '%#{company_name}%'
 ORDER BY score DESC;

