We were using below scope for ordering of our Payments which have a field 'number' of the like "PAY9994", "PAY9995", "PAY9996" etc.
scope :order_by_number, ->{order('number DESC')}
But we realized that it won`t work after our Payment number crosses "PAY9999". I could have used below scope, had it been a pure string of integers, but the prefix "PAY" is creating problems.
scope :order_by_number, ->{order('number::integer DESC')}
Any solution?
It you are able, it would be way more efficient to add a new column (with index) to signify order_number and run a rake task to back-fill the numbers and order on that instead.
Otherwise, you could also order first on length and then on number to get it to order right
scope :order_by_number, ->{ order('length(number) DESC, number DESC') }
If the prefix is always the same, you can trim it off:
scope :order_by_number, ->{ order("trim(leading 'PAY' FROM number)::integer DESC") }
Once you're sure that this gets you the order you want, you'll probably want to also add an expression-based index that matches this expression. That way, PostgreSQL will be able to order your records based on that index and won't have to run the expression for every row. See the PostgreSQL manual for help on that.
