QueryDSL: How to order by NEWID()?

Question

How can I write:

SELECT TOP 1 x
FROM y
ORDER BY NEWID()

using querydsl-sql?

(See https://stackoverflow.com/a/4980733/14731 for an explanation of what the query does)

@alas querydsl's scope is limited to building type-safe SQL statements. I've never used LINQ but I believe it has a much wider scope. — Gili, Apr 06 '15 at 20:20
But it doesn't seem to support specific sql server proprietary functions like newid(). So, basically you are looking for the querydsl-sql query to return a random record from a table, right? — alas, Apr 06 '15 at 20:30
@alas More precisely, I am looking for a way to invoke custom functions from within QueryDSL. — Gili, Apr 06 '15 at 20:32
so wouldn't TemplateExpression apply? see this : http://stackoverflow.com/questions/22984343/how-to-call-mysql-function-using-querydsl — alas, Apr 06 '15 at 20:34

Gili · Accepted Answer · 2015-04-16T12:22:44.020

4

I ended up doing the following:

import com.mysema.query.types.expr.StringExpression;
import com.mysema.query.types.template.StringTemplate;

/**
 * SQL Server specific expressions.
 *
 * @author Gili Tzabari
 */
public final class CustomExpressions {

    private static final StringExpression newId = StringTemplate.create("NEWID()");

    /**
     * Prevent construction.
     */
    private CustomExpressions() {
    }

    /**
     * @return NEWID()
     */
    public static StringExpression newId() {
        return newId;
    }
}

[...]

CustomExpressions expressions = new CustomExpressions();
new SQLQuery(connection, configuration).query().from(y).
  orderBy(expressions.newId().asc()).
  limit(1).uniqueResult(x);

edited Apr 16 '15 at 12:22

answered Apr 14 '15 at 18:55

Gili

86,244
97
390
689

+1 for the `StringTemplate` use, it's a superior solution to the one I offered. Personally I would favour a utility class for `CustomExpressions` - final, with a private constructor and a `public static StringExpression newId()` method. What are your thoughts? – Robert Bain Apr 14 '15 at 20:06
@RobertBain, for the current implementation of this class, I agree. For a singleton with mutable state, I tend to leave the class as-is and use IoC to instantiate it as a singleton. Anyway, I'll update the answer. Thanks for the feedback! – Gili Apr 14 '15 at 20:11
This looks fine, but the empty constructor should be private. – Timo Westkämper Apr 16 '15 at 07:28
If I were to be super-pedantic, `newId` should be `NEW_ID`. – Robert Bain Apr 16 '15 at 12:18

score 1 · Answer 2 · answered Apr 14 '15 at 18:49

It's not the most elegant solution but you could use the addFlag(QueryFlag.Position position, String flag) method, documented here.

E.G.

query.addFlag(QueryFlag.Position.END, "ORDER BY NEWID()");

I find when queryDSL refuses to play nice, and by "play nice" I mean when the API doesn't support what I'm trying to do, there are a few ways to essentially force a String into your query. This is the least bad way, in my experience.

QueryDSL: How to order by NEWID()?

2 Answers2