Rails PostgreSQL sort by integer value of string

Question

I made a strategic mistake when developing database architecture on my Rails app

Now I have to implement sorting by price feature using

MyModel.order('price DESC')

price is a string type in the database, which cause 50 to be greater than 2000 for example

Are there any ways to implement such .order() without changing database structure?

EDIT:

I switched to correct type (integer) for price column. It took me an hour only to refactor.

I think it is better to migrate column type from string to float if possible. Otherwise you should cast column: `ORDER BY price::float`. — Oleksandr Avoiants, Oct 21 '16 at 15:49
Yea Oleksandr Avoyants is right you really should just migrate the database table to have the right value. You are going to really hinder yourself if you just keep finding work arounds. — C dot StrifeVII, Oct 21 '16 at 15:54

score 17 · Accepted Answer · edited May 23 '17 at 11:47

17

With PostgreSQL you will want to cast your string to integer/float/decimal (after you decide you 100% will not go and change the column type to correct one):

MyModel.order('price::integer DESC')

Consider this answer to make it work fast.

edited May 23 '17 at 11:47

Community

1
1

answered Oct 21 '16 at 16:13

Andrey Deineko

51,333
10
112
145

Rails PostgreSQL sort by integer value of string

1 Answers1