Select from Select in QueryDSL

Question

I wonder if there is a way to phrase something like

SELECT `sum(<field>)` FROM (SELECT field from ... WHERE ...)

in QueryDSL(Version 4.x).

Let's say I have an Article "green Balloon" in all my Stores and I need to know how many "green Balloon" I have in total (I know it's a stupid example but it will do ;)).

The SQL could look like:

SELECT count(a.id) 
    FROM (
        SELECT art.id 
        FROM article art
        LEFT JOIN store s ON (art.storeId = s.id)
        WHERE art.name = 'green Balloon'
        GROUP BY s.id
    ) a;

How can I translate said SQL to QueryDSL?

Edit: As it was subject to confusion: Yes, the example is stupid. No I do not want to 'optimize' the SQL. All I need is some QueryDSL-Code generating the exact same (stupid) SQL. Or any other QueryDSL-Code generation any sort of SELECT ... FROM (SELECT ...) for that matter. If that is even possible.

There is a two year old post handling this topic, but apparently it was not possible then. Maybe it is now?

I guess it should rather count something else than _count(a.id)_ ... but whatever, that is not important. Hopefully. — Nikolas, Jan 17 '17 at 12:56
If you just want to count of green ballons in all stores then won't `SELECT count(*) FROM article art LEFT JOIN store s ON (art.storeId = s.id) WHERE art.name = 'green Balloon'` do you what you need? Why do you need to select from a select for that query? — DaveH, Jan 17 '17 at 13:11
As I said, the example is pretty dumb and (probably) not made-up well. BUT that should not matter, let's keep it simple. Currently I have an usecase in which I need to select 150k lines in my DB just to aggregate those lines in memory. In the end I display like 10 rows, just because I do not know how to properly "Select from select in QueryDSL" ;) — Nikolas, Jan 17 '17 at 13:20
In this example `SELECT COUNT(*) FROM (SELECT ...)` seems like overkill. Something like `SELECT SUM(a.quantity) FROM article a WHERE a.name = 'green Balloon'` would suffice for the provided example. This simplified query can be expressed in QueryDSL as `new JPAQuery
(entityManager).from(QArticle.article).where(QArticle.article.name.equals("green Balloon").select(QArticle.article.quantity.sum()).fetchOne()`. Note that an `EntityManager` instance is required to instantiate a `JPAQuery` in this case. — manish, Jan 20 '17 at 17:09
As I mentioned before, the SQL itself is NOT my problem. YES, the example is stupid. YES, it makes very little sense. It maters not, I just want someone to tell me if it is possible to write some QueryDSL generating the exact same (stupid) SQL, please. — Nikolas, Jan 23 '17 at 12:52

score 2 · Accepted Answer · answered Sep 04 '18 at 15:26

Accepting that the OP just want to know if that syntax can be duplicated in QueryDsl - consider the following.

It should be something like:

SQLExpression.select(Expressions.numberPath(Long.class, "ID").count())
             .from(SQLExpressions.select(QArticle.id().as("ID"))
                                 .from(QArticle)
                                 .leftJoin(QStore).on(QStore.id()
                                                 .eq(QArticle.storeId())
                                 .where(QArticle.name()
                                 .eq("green Balloon"))
             .as("SQ")

It might well be possible to simplify this - but it should get you most of the way there.

Select from Select in QueryDSL

1 Answers1