How to order varchar as int in Symfony2 Doctrine2?

Question

I have the following code:

$entity = $em->getRepository('MyBundle:MyEntity')
    ->createQueryBuilder('q')
    ->...
    ->orderBy('q.varcharCol')
    ->getQuery()
    ->getResult();

varcharProp is a varchar column containing values such as: 10, 10a, 101, 20, 300, 20b, 21, 200. I want to sort my entity by varcharProp but casting it as integer and I must do this ordering inside the query because I'm limiting the results.

It's possible to do something like this in Doctrine2?

entity->orderBy('ABS(q.varcharCol)')

Or changing varcharProp column type?

Answer 1

The best way to approach this is adding a select with the orderBy expression as a hidden column. I mean:

$entity = $em->getRepository('MyBundle:MyEntity')
    ->createQueryBuilder('q')
    ->addSelect('ABS(q.varcharCol) AS HIDDEN foo')
    ->...
    ->orderBy('foo')
    ->getQuery()
    ->getResult();

Thanks to @Qoop and @eggyal