Ruby Sort ActiveRecord - floats stored as strings like floats
Ruby on Rails 4 - Sort ActiveRecord string column as float.
I have a RecordModel table with a version column (row) that stores versions. The version is above the major.minor version. The problem is that the version will go from 9.0 to 10.0, but since it is stored as a string, how do you sort it as an integer?
RecordModel(id: integer, version: string, created_at: datetime, updated_at: datetime)
create_table "record_models", force: true do |t|
t.string "version"
t.datetime "created_at", null: false
t.datetime "updated_at", null: false
end
record_models = RecordModels.all
record_models.order(:version)
# results:
10.10
2.0
3.0
Update: - Database - postgresql - I cannot store as float because some version values are empty and some are xxx (e.g. 1.2.3)
source to share
I haven't tested this and I don't know how it will behave if a minor is missing, but I think it's worth a try.
RecordModel.all.select('*,
split_part(version, '.', 1)::int as major,
split_part(version, '.', 2)::int as minor,
split_part(version, '.', 3)::int as patch'
).order(major: :desc, minor: :desc, patch: :desc)
This is an update to work with postgresql
source to share
Since your database is PostgreSQL and this is Rails4, you have full access to the columns of the PostgreSQL array. This means that you can store the version number, for example 3.1.4
, as an integer array [3,1,4]
. Note that PostgreSQL arrays compare item by item, so sorting by array is done by The Right Thing.
So something like this:
create_table "record_models", force: true do |t|
t.integer "version", array: true
#...
end
storage should do the trick.
You can treat them as dot-delimited strings in and out by overriding accessors and mutator methods with something like:
def version=(v)
super(v ? v.split('.').map(&:to_i) : nil)
end
def version
super.to_a.join('.')
end
You might want to add some extra checks to make sure you get the formats that you expect.
If you want to store the versions as strings, you can also use regexp_split_to_array
casting to convert strings to arrays of integers as needed:
...order(%q{regexp_split_to_array(version, '\.')::int[]})
Careful with quoting there, you need literally '\.'
to the database, so %q{...}
is probably the cleanest quoting to use.
source to share