QueryDSL - 如何加入子查询的并集 [英] QueryDSL - how to join to a union of subqueries
问题描述
我正在使用QueryDSL构建SQL查询,该查询包含在联合中连接的多个子查询。这是我查询的基础:
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"));
这会编译,但在我尝试 query.count时会在运行时生成无效的SQL ()
。可能的错误:
This compiles, but generates invalid SQL at runtime when I attempt query.count()
. Likely mistakes:
- 子查询联合的语法。
- <$>之间的连接c $ c> .as(...)表达式,用于命名子查询结果列和
leftJoin
中使用的路径表达式。
- 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
.
推荐答案
修正了它。主要的错误是我错过了左连接中的
子句,但为了在 >条件我必须更加小心命名子查询。文档对构造访问子查询结果的路径有点了解,所以这是示例。
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);
我现在需要构建一个路径表达式来引用我的内部查询。我在路径中使用哪个类似乎并不重要,所以我选择了Void来强调这一点。
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));
还有一个表达的路径表达式
条款。请注意,我加入了union查询的 list()
,每个列都使用之前定义的 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")));
这篇关于QueryDSL - 如何加入子查询的并集的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!