ruby on rails - Custom scope in ActiveRecord - Reverse sorting is produced with invalid syntax -

ruby on rails - Custom scope in ActiveRecord - Reverse sorting is produced with invalid syntax -

rails version 4.1.6, postgres version not important.

i utilize custom sorting, strings come before integers , integers sorted numbers:

sample sorting:

a0101 bd330 be124 1 2 3 10

since there no direct way accomplish query interface, i've found postgres specific syntax which, in general, works fine:

default_scope { order("substring(entries.code, '[^0-9_].*$') asc"). order("(substring(entries.code, '^[0-9]+'))::int asc") }

for example, first record:

2.0.0p247 :001 > entry.first entry load (3.6ms) select "entries".* "entries" order substring(entries.code, '[^0-9_].*$') asc, (substring(entries.code, '^[0-9]+'))::int asc limit 1 => #<entry id: ...............>

however, when want reverse search, desc words raining on query string... quite annoying since haven't found way yet dispose off them:

2.0.0p247 :002 > entry.last entry load (0.8ms) select "entries".* "entries" order substring(entries.code desc, '[^0-9_].*$') desc, (substring(entries.code desc, '^[0-9]+'))::int desc limit 1 pg::error: error: syntax error @ or near "desc" line 1: ... "entries" order substring(entries.code desc, '[^0... ^ : select "entries".* "entries" order substring(entries.code desc, '[^0-9_].*$') desc, (substring(entries.code desc, '^[0-9]+'))::int desc limit 1 activerecord::statementinvalid: pg::error: error: syntax error @ or near "desc" line 1: ... "entries" order substring(entries.code desc, '[^0...

to more specific, believe not necessary, rid of desc within substring() methods...

edit:

i see in definition of reverse_sql_order, string split @ commas , , asc or desc applied there...

using extensive database-oriented functions in rails project never idea. kind of composite statements can drive insanely crazy.

order("substring(entries.code, '[^0-9_].*$') asc"). order("(substring(entries.code, '^[0-9]+'))::int asc")

imho, simplest , more effective solution helper column. define, instance, table column called weight type integer.

define model callback that, every time save object, stores in column 0 if value of sorting field string, digit if value number. here's sort index.

run sort queries against weight column. can index attribute, , queries much cleaner , faster. able sort desc or asc no complexity @ all.

ruby-on-rails postgresql activerecord