CODEWITHSUNDEEP
sqlruby-on-railsrubypostgresqlruby-on-rails-5
Umesh Malhotra
Umesh Malhotra

Reputation: 1047

How to order by integer value of string in ruby?

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?

Upvotes: 0

Views: 604

Answers (2)

codenamev
codenamev

Reputation: 2208

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') }

Upvotes: 2

ScottM
ScottM

Reputation: 10512

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.

Upvotes: 1

Related Questions