Python SQLalchemy order_by 1.9 and 1.10 in correct order?

user_5291566 0
Viewed 369

I have a table with a column version_number type TEXT.

Values look like this: 1.9 or 1.3 and now 1.10

The problem is that if I do .order_by(desc("version_number")) the result is ordered like this:

1.9
1.3
1.10 # this is handled like 1.1

I need:

1.10
1.9
1.3

Currently I dont know what to do. I dont want to change the column type and dont even know whether it would help. I also want to keep a good perfomance, as I have 1k rows~ for eahc request, which must be ordered.

Any ideas?

EDIT

.order_by(desc(func.string_to_array(BuildItems.version_number, '.'))

Does not work. 1.10 is still at the bottom

sqlalchemy sql-order-by python
1 Answers

You need to cast text array to an integer array

from sqlalchemy import Integer, cast 
from sqlalchemy.dialects.postgresql import ARRAY

.order_by(desc(cast(func.string_to_array(BuildItems.version_number, '.'), ARRAY(Integer))))
user_53418770
Related