Zendframework / PgSQL fetchAll orderby

Question

i have a pgsql table with fields id, identifier, name.

  id serial NOT NULL,
  identifier character varying(16),
  name character varying(128)

I want to fetchAll values from the table orderby identifier.

but identifier is having values

12, 100, 200, 50

and after $table->fetchAll(null, 'identifier'); is giving the result

100, 12, 200, 50

but I want the result as

12, 50, 100, 200

or using a direct query?

Answer 1

Try this:

$table->fetchAll(null, '(identifier+0)');

The +0 operation should make PostgreSQL cast the identifier value to an integer, so it sorts numerically instead of alphabetically.

The presence of parentheses should give Zend Framework a clue to treat the sorting argument as an expression, not a column name.

Answer 2

I think, this query will work for you

select * from tablename order by tableField::int