CODEWITHSUNDEEP
sqlruby-on-railssorting
rAzOr
rAzOr

Reputation: 320

Sort the filtered items on maximum matching conditions

In my rails webapp, a user can apply multiple filters for search. I would want the filtered items to be sorted by most matching conditions.

Ex: If a user searches for a car with Power steering, power windows and centre locking. The resulting items must be sorted as

  1. All matching conditions
  2. two conditions matching
  3. one condition matching

Whats standard way of doing this in rails?

Am doing the above by writing three different queries. One for each one. Then joining them. That seems very inefficient as the search option increases.

Thank you in advance.

Upvotes: 0

Views: 81

Answers (2)

Vishal Jain
Vishal Jain

Reputation: 1940

Hey you can try these way using you can write query.

used conditional clause in query to find occurrence given matching condition then order given records with given count

ModelName.select("*,((CASE WHEN (condition1) THEN 1 ELSE 0 END) + (CASE WHEN (condition2) THEN 1 ELSE 0 END)+( CASE WHEN (condition3) THEN 1 ELSE 0 END)) as match_occurance").where("condition1 or condition2 or condition3").order("match_occurance desc")

Upvotes: 1

Guy Segev
Guy Segev

Reputation: 1835

I think that in those cases it is better giving up ActiveRecord and using direct SQL for better performance.

You can use this query for retrieving and sorting your results in a single query:

SELECT * FROM 
   (SELECT
        *,
        (CASE WHEN condition1 THEN 1 ELSE 0) + 
        (CASE WHEN condition2 THEN 1 ELSE 0) +
        (CASE WHEN condition3 THEN 1 ELSE 0) as filterCount
    FROM cars 
    WHERE condition1
    OR condition2
    OR condition3) 
ORDER BY filterCount DESC

Upvotes: 2

Related Questions