Qfyilyi

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.

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.