Attempting to do a natural sort in Oracle with Zend_DB_Select gives error "ORA-01785: ORDER BY item must be the number of a SELECT-list expression"

Question

I've been trying to follow the solution on this page for implementing a natural sort in Oracle (11g2) but I cannot get it to work with Zend_Db_Select (ZF1) and a union query. My query looks something like this:

<?php

$sql[] = $db->select()
  ->distinct()
  ->from('table1', 'column_a')
  ->where('someVal LIKE ?', 'X%');

$sql[] = $db->select()
  ->distinct()
  ->from('table1', 'column_b')
  ->where('someVal LIKE ?', 'X%');

$union = $db->select()
   ->union(array($sql[0], $sql[1]))
   // here is the part from the other page
   ->order(array(
        new Zend_Db_Expr("to_number(regexp_substr(column_a, '^[0-9]+')) DESC"),
        new Zend_Db_Expr("to_number(regexp_substr(column_a, '[0-9]+$')) DESC"),
        'column_a DESC'
   ));

When I do this, I get error ORA-01785: ORDER BY item must be the number of a SELECT-list expression. I guess it's because both column_a and column_b become column_a as a result of the union(?) and it wants me to reference the column by number instead of name, but this works just fine if I take out the two lines that start with new Zend_Db_Expr() (i.e. it works with column_a DESC in the order by).

EDIT: Removed the close parentheses in to_number(regexp(substr(column_a, '^[0-9]+')) which originally closed prematurely right after column_a

Answer 1

Not a PHP person or a Zend person for that matter but the sample I looked at would have written it more like this (Note you repeated column_a in your original order() clause).

$sql1 = $db->select()
  ->distinct()
  ->from('table1', 'column_a')
  ->where('someVal LIKE ?', 'X%');

$sql2 = $db->select()
  ->distinct()
  ->from('table1', 'column_b')
  ->where('someVal LIKE ?', 'X%');

    $union = $db->select()
   ->union(array($sql1, $sql2))
   // here is the part from the other page
   ->order(array(
        new Zend_Db_Expr("to_number(regexp_substr(column_a), '^[0-9]+')) DESC"),
        new Zend_Db_Expr("to_number(regexp_substr(column_b), '[0-9]+$')) DESC"),
        'column_a DESC'
   ));