Possible to sort by letter grades?-CodePudding

I have a table that I'd like to sort by letter grade. The issue is that it sorts letters before letters with symbols. For example, it sorts: A, A , A-, B, B , B-, etc., but I would like it to sort A , A, A-, B , B, B-, etc. Is there a way to set do this?

CodePudding user response：

As idea you can check last letter of grade and depending on its value add some number to grade

You can add scope to your model

scope :order_by_grade, -> do
  sql = <<~SQL
    CASE RIGHT(grade, 1)
    WHEN '-' THEN grade || 3
    WHEN ' ' THEN grade || 1
    ELSE grade || 2
    END
  SQL

  order(sql)
end

And then apply to your model Work.order_by_grade

As another idea you can define some constant in the model with all variants and use index

And may be better sort from the worst to the best as ASC and from the best to the worst as DESC -- it's your choice

GRADES = %w[A  A A- B  ...]

scope :order_by_grade, -> do
  sql = 'CASE grade '

  GRADES.reverse_each.with_index do |grade, index|
    sql << sanitize_sql_array(['WHEN ? THEN ?', grade, index])
  end

  sql << ' END'

  order(sql)
end

And then apply to your model Work.order_by_grade or even Work.order_by_grade.reverse_order

CodePudding user response：

You may write the following.

arr = ["A", "B-", "B ", "A-", "B", "A "]

order = { ' ' => 0, nil => 1, '-' => 2 }

arr.sort_by { |s| [s[0], order[s[1]]] }
  #=> ["A ", "A", "A-", "B ", "B", "B-"]

Two arrays are compared for ordering with the method Array#<=>. See especially the third paragraph at the link.

Suppose

s1 = 'A'
s2 = 'A '

are being compared. For s1 we compute the array

[s[0], order[s[1]]]
  #=> ['A', order[nil]]
  #=> ['A', 1]

For s2

[s[0], order[s[1]]]
  #=> ['A', order[' ']]
  #=> ['A', 0]

['A', 0] <=> ['A', 1]
  #=> -1

'A ' is ordered before 'A'.