NumberExpression.random() is not working

[okihouse]

I have same issue
http://stackoverflow.com/questions/15869279/does-querydsl-not-support-rand

I'm trying to run this query.
query.from(test) .orderBy(NumberExpression.random().asc()) .limit(20) .list(test);

It is shown query
select test0_.id as id1_46_ from test test0_ order by random() asc

And I get this error
FUNCTION random does not exist

Mysql version is

mysql Ver 14.14 Distrib 5.7.11, for Linux (x86_64) using EditLine wrapper

I'm using querydsl

com.mysema.querydsl querydsl-apt 3.6.7 com.mysema.querydsl querydsl-jpa 3.6.7

mysql supported RAND() function.
http://dev.mysql.com/doc/refman/5.7/en/mathematical-functions.html#function_rand

So, for mysql, don't i have to use rand()?

[Shredder121]

Hi,
It seems to me that you are using JPQL (indicated by the querydsl-jpa dependency).
JPQL doesn't have a random() function, but you can make your JPA provider swap out random() for the actual random function specific to the dialect.

[okihouse]

@Shredder121

Thank you for letting me know.

Please can you show me how to use JPA provider to swap out the random()?

[Shredder121]

Which JPA Provider do you use?
For Hibernate you can extend a Dialect and register custom functions.

I just remembered that for others you can also use the special function(name, [arg,[arg...]]_ function, to call arbitrary SQL functions.
However, that doesn't work in Hibernate.

Then you can use Querydsl Template Expressions to serialize them into the query.

Expressions.numberTemplate(Double.class, "function('rand')");

[jxin19]

@Shredder121

thank you :D

below code is success result

.orderBy(Expressions.numberTemplate(Double.class, "function('rand')").asc())

[iamswain25]

if you have similar error like below, please use
Expressions.numberTemplate(Double.class, "RAND()"); instead.
I'm using java spring with mysql

PM org.hibernate.hql.internal.ast.ErrorCounter reportError
ERROR: line 8:25: expecting COMMA, found ')'
line 8:25: expecting COMMA, found ')'
	at antlr.Parser.match(Parser.java:211)
	at org.hibernate.hql.internal.antlr.HqlBaseParser.jpaFunctionSyntax(HqlBaseParser.java:4107)