Search code examples
sqlpostgresqlsortingrails-postgresqlpostgresql-9.5

Search results ordering based on rating and other values

I am struggling to build a complex ordering algorithm for the search results page.

I would like to order my items by rating (rating count, average rating), but I only want the rating take between 60-80% of the results page. One page has 12 items. They should be distributed randomly on a page.

I want to apply simple ordering as a secondary criteria, such as created_at field.

Does anybody have an idea how to do that?

Solution

  • I ended up using a solution which includes a chance of not rated items to end up in the middle of rated items. The idea of the algorithm is as follows:

    ORDER BY
CASE WHEN rating IS NOT NULL OR RANDOM() < 0.0x THEN 1 + RANDOM()ELSE RANDOM() END 
DESC NULLS LAST