CODEWITHSUNDEEP
ruby-on-railsrubyruby-on-rails-4activerecordnatural-sort
DRD
DRD

Reputation: 131

Rails Activerecord order by string containing numbers

I have the following query. ASSOC is the product category name.

q = params[:category]
@products = Product.where("ISBN = ?" ,q).order('ASSOC')

What I am getting is sorted like the following:

16 CHANNEL SECURITY DVRS
32 CHANNEL SECURITY DVRS
4 CHANNEL SECURITY DVRS
8 CHANNEL SECURITY DVRS

What I would like is:

4 CHANNEL SECURITY DVRS
8 CHANNEL SECURITY DVRS
16 CHANNEL SECURITY DVRS
32 CHANNEL SECURITY DVRS

Upvotes: 2

Views: 3782

Answers (1)

neuronaut
neuronaut

Reputation: 2709

The order method is just passing an ORDER BY clause to the database, and it's up to the DB to provide the actual order. Most databases should be able to split your string into pieces and order the numbers numerically instead of alphabetically. I'm not sure what DB you're using, but here's a question/answer showing how to do it for MySQL: MySQL order by string with numbers

You can send longer ORDER BY clauses from Rails by including the SQL directly in the call to order, e.g.:

order('SUBSTR(col_name FROM 1 FOR 1), CAST(SUBSTR(col_name FROM 2) AS UNSIGNED)')

As an alternative, you could avoid using the order method and then sort the results using Ruby (but that's going to be less efficient so you should only sort that way if your DB can't do what I described above).

Upvotes: 1

Related Questions