I want to return: Aa-Zz-symbol (like: ",1,2,3,+,-)
query.order_by(sqlalchemy.asc(getattr(schema.Study, name)))
it returns: symbol-Aa-Zz
example:
study table:
| 16SYB |
| "test" |
| a |
| z |
i want to return:
a
z
"test"
16SYB
but my code return:
"test"
16SYB
a
z
you can play with capital letters order using collation, e.g:
t=# with c(v) as (values('16SYB'),('"test"'),('a'),('z'),('A'))
select v from c order by v collate "en_US.utf8";
v
--------
16SYB
a
A
"test"
z
(5 rows)
vs:
t=# with c(v) as (values('16SYB'),('"test"'),('a'),('z'),('A'))
select v from c order by v collate "C";
v
--------
"test"
16SYB
A
a
z
(5 rows)
but in your case it's a little more complicated:
t=# with c(v) as (values('16SYB'),('"test"'),('a'),('z'),('A'))
select v from c order by case when v ~ '\d' then 9 when v ~ '"' then 8 else 0 end, v collate "en_US.utf8";
v
--------
a
A
z
"test"
16SYB
(5 rows)
of course you can play with this ordering rule yourself to expand or change it
update My proposed method does not uses sqlalchemy methods. I suppose you would need to use raw sql to implement it as I propose.
update2
As kindly proposed by Ilja Everilä, this should help:
col = getattr(schema.Study, name)
query.order_by(case([(col.op('~')(r'\d'), 9),
(col.op('~')('"'), 8)],
else_=0),
col.collate('en_US.utf8'))
sorry my ignorance - I can't check this due to total absence of sqlalchemy knowledge
