0

I have a field in an entity in string type, I need order result in my dql to value integer

I have a field in an entity in string type, I need the result sorted by that field but converted in an integer.

Some like this (MySQL Query): 

SELECT * FROM table1 ORDER BY CONVERT(code, UNSIGNED);

How do I create this query in doctrine?

UPDATE

I managed to do it with the cast function thanks to this post:

CASTING attributes for Ordering on a Doctrine2 DQL Query

I have created my own function to implement this feature.

Official doc in doctrine:

http://docs.doctrine-project.org/projects/doctrine-orm/en/latest/reference/dql-doctrine-query-language.html#adding-your-own-functions-to-the-dql-language

Ivan Javier Barranco Gavilan
  • 503
  • 3
  • 10
  • 25
  • 2
    It's very unclear what you're asking. Please, try to improve your question either in details than in "clarity". Thanks. – DonCallisto Jan 31 '18 at 08:28
  • @DonCallisto table1.code is a string type, i need conver this value in integer for sort this value. Sorry for my english – Ivan Javier Barranco Gavilan Jan 31 '18 at 08:41
  • Possible duplicate of [Using DQL functions inside Doctrine 2 ORDER BY](https://stackoverflow.com/questions/24765826/using-dql-functions-inside-doctrine-2-order-by) – habibun Jan 31 '18 at 09:14

3 Answers3

2

AFAIK You can't do it directly . Doctrine don't support native mysql functions (convert ,day,month etc). Idea od doctrine is to be able to talk with many different databases - and it's why there isn't any native functions.

but

you can do it on your own.

Some years ago i needed data functions (day/month etc ) in doctrine so i manage to add it do doctrine .

look here :

https://github.com/poznet/SF2Core/blob/master/src/Poznet/CoreBundle/Dql/Year.php or https://github.com/beberlei/DoctrineExtensions

seen solutions for conver too , but never tested it , look here

https://gist.github.com/liverbool/6345800

Michał G
  • 2,234
  • 19
  • 27
1
<?php

class ConvertUsing extends FunctionNode
{
    public $field;

    public $using;

    public $charset;

    /**
     * @override
     */
    public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
    {
        return sprintf('CONVERT(%s USING %s)',
            $sqlWalker->walkArithmeticPrimary($this->field),
            //$sqlWalker->walkSimpleArithmeticExpression($this->using), // or remove USING and uncomment this
            $sqlWalker->walkSimpleArithmeticExpression($this->charset)

        );
    }

    /**
     * @override
     */

    public function parse(\Doctrine\ORM\Query\Parser $parser)
    {
        $parser->match(Lexer::T_IDENTIFIER);
        $parser->match(Lexer::T_OPEN_PARENTHESIS);
        $this->field   = $parser->ArithmeticPrimary();
        // adopt use bypass validate variable of parse by using AliasResultVariable ...!!
        $this->using   = $parser->AliasResultVariable();
        $this->charset = $parser->AliasResultVariable();
        $parser->match(Lexer::T_CLOSE_PARENTHESIS);
    }
}
Pang
  • 9,564
  • 146
  • 81
  • 122
Fahad Shahzad Bhatti
  • 21
  • 9
-1

If you only want to sort your query you can sort the code even if it's a string like this.

SELECT * FROM table1 ORDER BY code ASC; <- for ascending

SELECT * FROM table1 ORDER BY code DESC; <- for descending

If you want to convert it to integer try this 

SELECT * FROM table1 ORDER BY (SELECT CONVERT(int, code));

Just make sure your codes are all numbers so you will not get an error. I hope it helps.

Noriel
  • 218
  • 1
  • 7