Search code examples
ruby-on-railsranking

Custom ranking based on multiple criteria in Rails


I need to rank records in a database table based on two columns:

availability_date

and

updated_at

They're both datetime-type columns, but the conditions for ranking are not linear in time. So, for example, in a scale of 0 to 10, 'updated_at' values:

between 14 and 30 days ago would get a 10,

whereas 13 to 9 days ago would get 9.5,

31 to 60 would get 9,

etc.

The same applies to the 'availability_date' column.

The final ranking/ordering result should be the average of values of both scales for each record.

I'm trying to write that logic in a controller and ideally wanted to avoid adding columns in the database to build the indexes.

I'm using Ruby 2.5.1 and Rails 5.2.

Does anyone know if that's feasible?

Thanks a lot in advance.


Solution

  • As your question currently stands, of course it is feasible. Try using sort_by, where you give a block specifying how to sort your data.

    In this block you should write exactly the logic you are stating: translate the date to a value depending on how old it is and getting the average of those two values.

    I can imagine something like:

    def date_to_val(date)
      days_ago = (Time.now - date).to_i / 1.day
      return 10 if days_ago.between?(14, 30)
      return 9.5 if days_ago.between?(9, 13)
      return 9 if days_ago.between?(31, 60)
      # and so on
    end
    
    def my_controller_action
      @records = SomeClass.where(some_attr: some_val)
      ordered = @records.sort_by do |e| 
        [
          date_to_val(e.created_at), 
          date_to_val(e.availability_date)
        ].sum / 2.0
      end
      # do something with ordered
    end
    

    Now, if you want to do it purely with SQL queries, then that is another question.