JPQL / QueryDSL: join subquery and get aliased column

Question

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?

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.

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...

Here's my code:

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

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!

Thanks!

Edit: 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;

Answer 1

I know that this question is old and already has an accepted answer, but judging from this question, it seems to still be troubling guys. See my answer in the same question. The use of JoinFlag in the join() section and Expression.path() is able to achieve left-joining a subquery. Hope this helps someone.

Answer 2

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.

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

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

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

Answer 3

QueryDsl does not support subQuery in join but you can achieve this via following way:

We wanted to achieve the following query:

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

Map a View or SQL query to JPA entity:

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;
}

then use the equivalent QueryDSl entity in the class just like the regular entity:

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