Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
MySQL RAND() function in Doctrine2 DQL
<?php
$config = new \Doctrine\ORM\Configuration();
/** OTHER STUFF */
$config->addCustomNumericFunction('RAND', 'My\Custom\Doctrine2\Function\Rand');
/** CONTINUES */
<?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()';
}
}
@Ocramius

This comment has been minimized.

Show comment Hide comment
@Ocramius

Ocramius Apr 14, 2011

Owner

Ocramius commented Apr 14, 2011

@lmlopez

This comment has been minimized.

Show comment Hide comment
@lmlopez

lmlopez Jun 26, 2013

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 commented Jun 26, 2013

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 '('

@wardpeet

This comment has been minimized.

Show comment Hide comment
@wardpeet

wardpeet Jul 10, 2013

@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');

@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');
@Abhoryo

This comment has been minimized.

Show comment Hide comment
@Abhoryo

Abhoryo Jul 24, 2013

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. "

Abhoryo commented Jul 24, 2013

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. "

@Abhoryo

This comment has been minimized.

Show comment Hide comment
@Abhoryo

Abhoryo Jul 24, 2013

@lmlopez I have the same error.

Abhoryo commented Jul 24, 2013

@lmlopez I have the same error.

@Ocramius

This comment has been minimized.

Show comment Hide comment
@Ocramius

Ocramius Feb 28, 2014

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

Owner

Ocramius commented Feb 28, 2014

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

@jeroensen

This comment has been minimized.

Show comment Hide comment
@jeroensen

jeroensen Mar 25, 2014

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?

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?

@gabrielthakid

This comment has been minimized.

Show comment Hide comment
@gabrielthakid

gabrielthakid Apr 16, 2014

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 130: Error: Expected end of string, got '('

Has someone fixed this yet?

@nocomment17

This comment has been minimized.

Show comment Hide comment
@nocomment17

nocomment17 May 30, 2014

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

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

@gusdecool

This comment has been minimized.

Show comment Hide comment
@gusdecool

gusdecool Jan 16, 2015

This code messed up when using with JOIN query.

This code messed up when using with JOIN query.

@dvapelnik

This comment has been minimized.

Show comment Hide comment
@dvapelnik

dvapelnik Oct 22, 2015

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

@Ocramius why?

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

@Ocramius why?

@BenMorel

This comment has been minimized.

Show comment Hide comment
@BenMorel

BenMorel Feb 16, 2016

@jeroensen @dvapelnik IIRC, sorting by RAND() forces a scan of all matching records, as RAND() is just a numerical function that returns a random number. If there is a small number of matching records, it should be alright. But if you have millions of matching records and attempt something like ORDER BY RAND() LIMIT 10, you might be in trouble, as the database will have to scan all these rows, remember all of them along with the number returned by RAND() for each of them, then extract the few ones with the lowest number (someone correct me if I'm wrong on the details). This is very resource intensive.

There are more efficient ways to select randomly. See for example:
http://jan.kneschke.de/projects/mysql/order-by-rand/

@jeroensen @dvapelnik IIRC, sorting by RAND() forces a scan of all matching records, as RAND() is just a numerical function that returns a random number. If there is a small number of matching records, it should be alright. But if you have millions of matching records and attempt something like ORDER BY RAND() LIMIT 10, you might be in trouble, as the database will have to scan all these rows, remember all of them along with the number returned by RAND() for each of them, then extract the few ones with the lowest number (someone correct me if I'm wrong on the details). This is very resource intensive.

There are more efficient ways to select randomly. See for example:
http://jan.kneschke.de/projects/mysql/order-by-rand/

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment