JPQL/QueryDSL:加入子查询并获取别名列 [英] JPQL / QueryDSL: join subquery and get aliased column

问题描述

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

I'm trying to get an average for a count on a groupBy by joining with a subquery. Don't know if that the right way to go at all but I couldn't anything about subqueries other than the mysema doc.

场景: 客户平均每件产品有多少订单? 含义:客户订购产品.因此，客户多次订购特定产品(数量).客户为任何产品下的平均订单数是多少?

Scenario: How many orders per product did a customer do on average? Meaning: A Customer orders products. So a customer ordered a specific product a number of times (count). What's the average number of orders that customer placed for any product?

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

Might sound a bit hypothetical, in fact it's just part of a prototype, but it made me wonder, how to get a reference to a custom column created within a subquery with the fancy QueryDSL from Mysema.

在SQL中，您只需为count列提供别名，然后使用第二个ID列进行联接. QueryDSL也具有"as()"方法，但是我不知道如何检索该列，而且我不知道它如何将一个查询与另一个查询连接起来，因为query.list()仅获得一个列表，但包含一些联接接受它的原因.感觉不对...

In SQL you just give the count column an alias and join using a second ID column. QueryDSL has the "as()" method as well but I have no Idea, how to retrieve that column plus I dont't see how it can join one query with anothers, since query.list() just gets a list but for some reason the join accepts it. Feels wrong...

这是我的代码:

    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());

再次:如何加入子查询? 我如何获取别名的计数"列以进行更多的汇总，例如avg(我的群组正确吗?) 可能是我在这方面还有其他错误，因此对您有所帮助！

Again: How do I join with a subquery? How do I get the aliased "count" column to do more aggregation like avg (is my group right btw?) Might be that I have some other errors in this, so any help appreciated!

谢谢！

修改: 我想看看QueryDSL产生的那种本地SQL:

That's kind of the native SQL I'd like to see QueryDSL produce:

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;

推荐答案

不允许在join子句中使用子查询.在JPQL中，仅在WHERE and HAVING部分中允许子查询. Querydsl JPA查询中的联接方法签名太宽.

Using subqueries in the join clause is not allowed. in JPQL, subqueries are only allowed in the WHERE and HAVING part. The join method signatures in Querydsl JPA queries are too wide.

由于此查询需要两个级别的分组，因此可能无法用JPQL/Querydsl JPA表示.

As this query needs two levels of grouping, maybe it can't be expressed with JPQL / Querydsl JPA.

我建议使用Querydsl JPA本机查询支持编写此查询.

I'd suggest to write this query using the Querydsl JPA Native query support.

由于Querydsl JPA在内部使用JPQL，因此受到JPQL的表现力的限制.

As Querydsl JPA uses JPQL internally, it is restricted by the expressiveness of JPQL.

这篇关于JPQL/QueryDSL:加入子查询并获取别名列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！