Java QueryDSL - 如何加入子查询的联合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22923560/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
QueryDSL - how to join to a union of subqueries
提问by Adrian Cox
I'm building a SQL query with QueryDSL that contains several subqueries joined in a union. This is the base of my query:
我正在使用 QueryDSL 构建一个 SQL 查询，其中包含几个加入联合的子查询。这是我查询的基础：
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:
这可以编译，但是当我尝试query.count(). 可能的错误：
- 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 theleftJoin.
- 子查询联合的语法。
- 的之间的连接
.as(...)名称子查询结果列和路径表达式在所使用的表达leftJoin。
采纳答案by Adrian Cox
Fixed it. The main bug was that I'd missed out the onclause in the left join, but in order to express the oncondition 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.
修复。主要错误是我遗漏了on左连接中的子句，但为了表达on条件，我必须更加小心地命名子查询。该文档对构建访问子查询结果的路径有一些了解，所以这是示例。
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.
我现在需要构建一个路径表达式来引用我的内部查询。我为路径使用哪个类似乎并不重要，所以我选择了 Void 来强调这一点。
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 onclause. Note that I join to a list()of the union query, with each column selected using the innerUnionpath defined earlier.
以及进一步的路径表达式来表达on子句。请注意，我加入了一个list()联合查询，使用innerUnion之前定义的路径选择了每一列。
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")));
