sorting on strings that need to be interpreted as numbers

[dcmorse]

trafficstars

Hi, Thank you for ajax-datatables-rails - it is awesome!

I have a database column called study_time, that has string values of "0-5", "6-10", "11-20", "21-40", "40+", and nil. Users are asking to sort by this column. In my datatable I have:

class CourseApplicationDatatable < AjaxDatatablesRails::ActiveRecord

  def view_columns
    # Declare strings in this format: ModelName.column_name
    # or in aliased_join_table.column_name format
    @view_columns ||= {
      ...
      study_time: { source: "CourseApplication.study_time" },
      ...
    }
  end

  def data_hash(course_applications)
    course_applications.map do |course_application|
      {
        ...
        study_time: course_application.study_time,
        ...
      }
    end
  end
  ...
end

This kind-of works, but I would like the sort order to be

"0-5", "6-10", "11-20", "21-40", "40+", but it is

"0-5", "11-20", "21-40", "40+", "6-10", because the strings are interpreted alphabetically. Can anyone think of a way to do this without rebuilding the column format of study_time?

Thanks for any pointers you could give.

[aldent95]

Not sure if you ever sorted this, but here is the solution I came up with today for a similar problem.

For the view_columns, it just needs to be set as normal for source

You then need to override the sort_records method to replace the sort query for that column. Replace pilots.pilot_id with whatever your table and column are from the source section of view_columns. I.E. mine was Pilot.pilot_id in source so that maps to pilots.pilot_id in the sort records function.

  def sort_records(records)
    sort_by = datatable.orders.inject([]) do |queries, order|
      column = order.column
      if column&.orderable?
        if column.sort_query.include? 'pilots.pilot_id'
          if order.query(column.sort_query).include? 'ASC'
            queries << 'orderable_column ASC'
          else
            queries << 'orderable_column DESC'
          end
        else
          queries << order.query(column.sort_query)
        end
      end
      queries
    end
    records.order(Arel.sql(sort_by.join(', ')))
  end

Then, add a select to your get_raw_records to set up the orderable_column field so you can sort it.

    Pilot.includes(
        :rank,
        :user_type,
        :hub
    ).references(:rank, :user_type, :hub).select('replace(pilots.pilot_id, \'MLM\', \'\')::int as orderable_id').distinct

I'm using postgres so different databases might need different syntax, but the gist of the select function there is to remove any extra text (My column had data like MLM1, MLM1100 etc..), then cast it as an int.

You would want to replace the - in the middle, so just replace 'MLM' with '-' There might be some edge cases you would need to handle for specific data. I.E. replace the 40+ with 40000 so it doesn't sort itself between 05 and the 610.

Hopefully that helps you or anyone else coming across this.

After that, it worked like a charm for me :)