JPQL / QueryDSL：加入子查询并获取别名列

Question

我试图通过加入子查询来获取 groupBy 计数的平均值。不知道这是否是正确的方法，但除了 mysema 文档之外，我对子查询一无所知。

设想： 客户平均每种产品有多少订单？ 含义：客户订购产品。因此，客户多次（计数）订购特定产品。客户对任何产品下的平均订单数是多少？

听起来可能有点假设，事实上它只是原型的一部分，但它让我想知道如何使用 Mysema 的精美 QueryDSL 获取对在子查询中创建的自定义列的引用。

在 SQL 中，您只需为计数列指定一个别名，然后使用第二个 ID 列进行连接。 QueryDSL 也有“as()”方法，但我不知道如何检索该列，而且我不知道它如何将一个查询与另一个查询连接起来，因为 query.list() 只是获取一个列表，但对于某些连接接受它的原因。感觉不对......

这是我的代码：

    JPQLQuery query = createJPQLQuery();

    QOrdering qOrdering = QOrdering.ordering;
    QProduct qProduct = QProduct.product;
    QCustomer qCustomer = QCustomer.customer;           

    // how many of each product did a customer order?
    HibernateSubQuery subQuery = new HibernateSubQuery();
    subQuery.from(qOrdering).innerJoin(qOrdering.product,qProduct).innerJoin(qOrdering.customer, qCustomer);
    subQuery.groupBy(qCustomer,qProduct).list(qCustomer.id,qProduct.id,qProduct.count());

    // get the average number of orders per product for each customer
    query.from(qCustomer);      
    query.innerJoin(subQuery.list(qCustomer.id,qOrdering.count().as("count_orders")));      
    query.groupBy(qCustomer.id);
    return (List<Object[]>) query.list(qCustomer.firstname,subQuery.count_orders.avg());

再次：如何加入子查询？ 如何获得别名“count”列以进行更多聚合，例如 avg（顺便说一句，我的组正确吗？） 可能我还有其他一些错误，所以任何帮助表示赞赏！

谢谢！

编辑： 这就是我希望 QueryDSL 生成的原生 SQL：

Select avg(numOrders) as average, cust.lastname from
customer cust
inner join
(select count(o.product_id) as numOrders, c.id as cid, p.name
from ordering o
inner join product p on o.product_id=p.id
inner join customer c on o.customer_id=c.id
group by o.customer_id, o.product_id) as numprods
on cust.id = numprods.cid
group by numprods.cid
order by cust.lastname;

Answer 1

不允许在 join 子句中使用子查询。在JPQL中，子查询只允许出现在WHERE和HAVING部分。 Querydsl JPA 查询中的连接方法签名太宽。

由于此查询需要两级分组，因此可能无法使用 JPQL / Querydsl JPA 来表达。

我建议使用 Querydsl JPA Native 查询支持来编写此查询。

由于Querydsl JPA内部使用JPQL，因此受到JPQL表达能力的限制。

Answer 2

我知道这个问题很旧并且已经有一个公认的答案，但从 这个问题，看来还是困扰着小伙伴们。请参阅我在同一问题中的回答。在join()部分和Expression.path()中使用JoinFlag可以实现子查询的左连接。希望这对某人有帮助。

Answer 3

QueryDsl 不支持 join 中的 subQuery，但您可以通过以下方式实现：

我们想要实现以下查询：

select A.* from A join (select aid from B group by aid) b on b.aid=A.id;

将视图或 SQL 查询映射到 JPA 实体：

import lombok.Setter;
import org.hibernate.annotations.Subselect;
import org.hibernate.annotations.Synchronize;

import javax.persistence.Entity;
import javax.persistence.Id;

@Entity
@Getter
@Setter
@Subselect("select aid from B group by aid")
@Synchronize("B")
public class BGroupByAid {

    @Id
    private Integer aId;
}

然后在类中使用等效的 QueryDSl 实体，就像常规实体一样：

JPAQuery<QAsset> query = new JPAQuery<>(entityManager);  
QBGroupByAid bGroupById = QBGroupByAid.bGroupByAid;

 List<A> tupleOfAssets =
            query.select(A)
        .from(A).innerJoin(bGroupById).on(bGroupById.aId.eq(A.aId))
        .fetchResults()
        .getResults();
        

Answer 4

您还可以使用 blazebit，它也支持连接中的子查询。我已经尝试过并且有效。您可以像这样创建 SubQueryExpression fe

SubQueryExpression<Tuple> sp2 = getQueryFactory().select(entity.id,
                        JPQLNextExpressions.rowNumber().over().partitionBy(entity.folId).orderBy(entity.creationDate.desc()).as(rowNumber))
                .from(entity)
                .where(Expressions.path(Integer.class, rowNumber).eq(1));

，然后像这样加入它：

return getBlazeQueryFactory()
                .select(entity1, entity)
                .from(entity1)
                .leftJoin(sp2, entity).on(entity.id.eq(entity1.id)).fetch();

我在这里只是简单的例子。所以也许它没有完全的意义，但也许会有帮助。

也不要混淆，它会在生成的选择中产生并集。这只是为了命名子查询中的列，您可以在此处阅读有关此联合的更好解释：LEFT JOIN SUBQUERY 中的 Blaze-Persistence GROUP BY 与根查询中的 COALESCE