4 Replies Latest reply: Oct 16, 2013 12:28 PM by e3aeb444-ff39-44cc-b23c-747d1945ca99 RSS

    ORDER BY RAND() with EclipseLink ??

    436717
      how to implement ORDER BY RAND() with EclipseLink ?

      * I want to execute a MySql query that returns a collection sorted randomly ....
        • 1. Re: ORDER BY RAND() with EclipseLink ??
          JamesSutherland
          You can do this using an Expression query.

          There is no direct support for rand() but you can access using getFunction() or using fromLiteral(). getFunction() is normally used for functions that take one argument, so fromLiteral() may work better.
          You could use getFunction() if you defined your own ExpressionOperator for rand().

          i.e
          ReadAllQuery query = new ReadAllQuery(Employee.class);
          query.addOrdering(query.getExpressionBuilder().fromLiteral("RAND()", query.getExpressionBuilder()));


          Please also log a bug on EclipseLink to have rand() support added.



          ---
          James : http://www.eclipselink.org : http://en.wikibooks.org/wiki/Java_Persistence
          • 2. Re: ORDER BY RAND() with EclipseLink ??
            436717
            very nice, thank you!

            * I don't agree with a native support of the RAND() function since there is no standard way of doing that in SQL. So, for each DB you need a different function. To have a chance to call these functions with EclipseLink seems enough.
            • 3. Re: ORDER BY RAND() with EclipseLink ??
              436717
              The online sample:

              http://fgaucho.dyndns.org:8080/arena-http/ads

              F5 to refresh and see the order of the advertisements changing :)

              and the same data loaded under a GUI:

              http://fgaucho.dyndns.org:8080/arena-dwr/

              * the gui was not yet updated to support the dynamic order, but it is coming soon :)

              Edited by: Felipe Gaúcho on Nov 20, 2009 10:12 AM

              Edited by: Felipe Gaúcho on Nov 20, 2009 10:12 AM
              • 4. Re: ORDER BY RAND() with EclipseLink ??
                e3aeb444-ff39-44cc-b23c-747d1945ca99

                This is an old post but I was trying to achieve the same with Criteria API and couldn't find the "right" way.

                This was the only post that helped when googling for a solution, hope this can help to someone else too.

                 

                Currently this works for me using Criteria API:

                cq.orderBy(criteriaBuilder.asc(criteriaBuilder.function("RANDOM", Void.class)));

                 

                Using what James post suggests with Criteria API:

                cq.orderBy(criteriaBuilder.asc(criteriaBuilder.literal("RANDOM()")));

                will not work.

                 

                Note: I'm using PostgreSQL so that's why I use RANDOM function instad of RAND.

                 

                Regards

                --

                Marc Nuri