Edit Question - KnowledgeHub

Title Body

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 righ

Tags (comma-separated) Cancel