Q:

Advanced Active Record order by using regex

I am trying to query my Order model for all records but I want them ordered in a very specific way.
Database is a PostgreSQL database.
I want all records that start with NY first, NYN next, then all remaining records follow.
I think I'm trying to do something like this:
Order.all.order('order_number /^NY/, /^NYN/')

An example of data:
NY-1111111
NYN-1234567
P-000000
P0000000
SS0232131
NYN16151202

The order I want is:
NY-1111111
NYN16151202
NYN-1234567
P0000000
P-000000
SS0232131

A:

Here's one way you might do this in SQL:
SELECT * FROM orders
  ORDER BY
    CASE
      WHEN order_number LIKE 'NYN%' THEN 1
      WHEN order_number LIKE 'NY%' THEN 0
      ELSE 2
    END,
    order_number;

This can be translated pretty directly to an ActiveRecord query:
Order.order("
  CASE
    WHEN order_number LIKE 'NYN%' THEN 1
    WHEN order_number LIKE 'NY%' THEN 0
    ELSE 2
  END,
  order_number
")

Here's another possibility using PostgreSQL's POSIX regular expressions. I'm not sure how it compares to the above in performance:
Order.order("
  CASE SUBSTRING(order_number FROM '^NYN?')
    WHEN 'NY' THEN 0
    WHEN 'NYN' THEN 1
    ELSE 2
  END,
  order_number
")

