QueryDSL - how to join to a union of subqueries

Question

I'm building a SQL query with QueryDSL that contains several subqueries joined in a union. This is the base of my query:

QTransaction t = QTransaction.transaction;
query = query.from(t).where(t.total.gt(BigDecimal.ZERO));

I then have several subqueries to obtain client names associated with a transaction. I've cut down to two for the example:

SQLSubQuery subQuery = new SQLSubQuery();
subQuery = subQuery.from(t).join(t.fk462bdfe3e03a52d4, QClient.client);
ListSubQuery clientByPaid = subQuery.list(t.id, bt.paidId, QClient.client.name.as("clientname"));

subQuery = new SQLSubQuery();
subQuery = subQuery.from(t).where(t.paidId.isNull(), t.clientname.isNotNull());
ListSubQuery clientByName = subQuery.list(t.id, Expressions.constant(-1L), t.clientname.as("clientname"));

How do I union these together, and join the union with my main query? This is my current attempt:

subQuery = new SQLSubQuery();
subQuery = subQuery.from(subQuery.unionAll(clientByPaid,clientByName).as("namequery"));


query = query.leftJoin(subQuery.list(
            t.id, Expressions.path(Long.class, "clientid"),
                    Expressions.stringPath("clientname")),
                    Expressions.path(List.class, "namequery"));

This compiles, but generates invalid SQL at runtime when I attempt query.count(). Likely mistakes:

• The syntax for the union of subqueries.
• The connection between the .as(...) expression that names the subquery result columns and the path expression used in the leftJoin.

Answer 1

Fixed it. The main bug was that I'd missed out the on clause in the left join, but in order to express the on condition I had to be much more careful about naming the subqueries. The documentation is a little light on constructing paths to access subquery results, so here's the example.

The first query in the union sets the column names:

SQLSubQuery subQuery = new SQLSubQuery();
subQuery = subQuery.from(t).join(t.fk462bdfe3e03a52d4, QClient.client);
ListSubQuery clientByPaid = subQuery.list(t.id.as("id"), t.paidId.as("clientid"),
                                QClient.client.name.as("clientname"));

subQuery = new SQLSubQuery();
subQuery = subQuery.from(t).where(t.paidId.isNull(), t.clientname.isNotNull());
ListSubQuery clientByName = subQuery.list(t.id, Expressions.constant(-1L), 
                                  t.clientname);

I now need to build a path expressions to refer back to my inner query. It doesn't seem to matter which class I use for the path, so I've picked Void to emphasize this.

subQuery = new SQLSubQuery();
Path innerUnion = Expressions.path(Void.class, "innernamequery");
subQuery = subQuery.from(subQuery.union(clientByPaid,clientByName).as(innerUnion));

And a further path expression to express the on clause. Note that I join to a list() of the union query, with each column selected using the innerUnion path defined earlier.

Path namequery = Expressions.path(Void.class, "namequery");
query = query.leftJoin(subQuery.list(
                Expressions.path(Long.class, innerUnion, "id"),
                Expressions.path(Long.class, innerUnion, "clientid"),
                Expressions.stringPath(innerUnion, "clientname")),
              namequery)
          .on(t.id.eq(Expressions.path(Long.class, namequery, "id")));