0

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

Community
  • 1
  • 1
fronzee
  • 1,668
  • 2
  • 21
  • 32
  • No, it thinks you *are* referencing the column by position, but I don't see how unless the expression is being evaluated (to a number that happens to be higher than the number of columns selected, ie 1), and that doesn't seem to be how it's supposed to work. (I don't use this, very quick doc look-up). Don't you have too many closing parentheses in each expression though? Doesn't explain that error but still looks wrong; guess from changing you real code for posting? – Alex Poole Jun 02 '14 at 19:11
  • @AlexPoole Yes, you're right, I had one too many close parentheses. Updated it (see edit comment). Thanks. – fronzee Jun 02 '14 at 19:23

1 Answers1

0

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'
   ));
JJF
  • 2,681
  • 2
  • 18
  • 31