Created

Embed URL

HTTPS clone URL

SSH clone URL

You can clone with HTTPS or SSH.

Download Gist

MySQL RAND() function in Doctrine2 DQL

View Rand.php
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
<?php
namespace My\Custom\Doctrine2\Function;
/**
* RandFunction ::= "RAND" "(" ")"
*/
class Rand extends FunctionNode
{
public function parse(\Doctrine\ORM\Query\Parser $parser)
{
$parser->match(Lexer::T_IDENTIFIER);
$parser->match(Lexer::T_OPEN_PARENTHESIS);
$parser->match(Lexer::T_CLOSE_PARENTHESIS);
}
public function getSql(\Doctrine\ORM\Query\SqlWalker $sqlWalker)
{
return 'RAND()';
}
}
View Rand.php
1 2 3 4 5 6
<?php
$config = new \Doctrine\ORM\Configuration();
/** OTHER STUFF */
$config->addCustomNumericFunction('RAND', 'My\Custom\Doctrine2\Function\Rand');
/** CONTINUES */
Owner

I am using this code in my project, but I have this error, any idea?

Message: [Syntax Error] line 0, col 130: Error: Expected end of string, got '('

@lmlopez it is working but you need to change something in your querybuilder or dql

$q = $this->createQueryBuilder('e')
    ->addSelect('RAND() as HIDDEN rand')
    ->orderBy('rand');

Warning @wardpeet: The doc says : "You cannot use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. "

@lmlopez I have the same error.

Owner

Sorting by rand is a BAD idea. Don't do it!

Why is sorting by RAND() a bad idea?!? When you need some random data from the database this is actually a very simple and fast solution (since ordering by rand doesn't force any index to be created by the database (btree indexes and the like that is)). So it's actually a very cheap solution in terms of processor cycles.

If you would implement this in code it would not only take more time (to implement) but also more processor cycles.

Care to comment on that?

I have the same problem
Message: [Syntax Error] line 0, col 130: Error: Expected end of string, got '('

Has someone fixed this yet?

I have the same problem
Message: [Syntax Error] line 0, col 226: Error: Expected end of string, got '('

Has someone fixed this yet?

http://www.doctrine-project.org/blog/doctrine2-custom-dql-udfs => 404

This code messed up when using with JOIN query.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Something went wrong with that request. Please try again.