CODEWITHSUNDEEP
ruby-on-railspostgresql
Ellbells
Ellbells

Reputation: 91

How to Order by relevance in a SQL query in Rails?

I am using a SQL query for a specified and faster search in my Rails app, currently I have the following query that works wonderfully for gathering the data from the initial search:

SELECT * FROM selected_tables WHERE field1 LIKE '%#{present_param}' AND field2 LIKE '%#{present_param2}'

And so on like that, with each LIKE line only appearing if the relevant parameter is present from the form.

So I am now able to get back a large amount of results from this query, but they're not ordered in any helpful way. I need some way of ordering the results based on their relevance to the original user input from the form, but I can't seem to find anything on google about it. Is there a way in SQL (specifically postgresql) that I can order the results based on this?

To be clear, when I say relevance I mean that a given search keyword should be in the title or company name for the result, not just present somewhere in the content.

For example: if you search "Sony" you get Sony Electronics first, not another listing containing Sony somewhere in the middle of its name.

Upvotes: 2

Views: 1435

Answers (1)

Ellbells
Ellbells

Reputation: 91

I ended up using a series of Case/When statements that were weighted with various integer scores to apply priority to my results. They work wonderfully and turned out something like this:

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;

Upvotes: 7

Related Questions