QueryDsl - How create inner join with sorted and grouped table

I have to find Salesmen that have sold some itemType. I created method (see below).

But client told me that he wants to find Salesmen by LAST sold itemType.
DB schema:

My attempts: we have in table ORDERS date column, so in normal SQL query I can do double subquery and it should work.

Double, because first I'm sorting by date, then group by salesman - that returns list with only last sold items.

SELECT * 
FROM SALESMEN   
JOIN 
	(SELECT *
	 FROM 
		 (SELECT *
		  FROM ORDERS
		  ORDER BY ORDERS.date)
	 GROUP BY ORDERS.salesman_id) ON SALESMEN.id = ORDERS.salesman_id
WHERE ORDERS.item_type = "CAR" 

Unfortunately, queryDSL can do subquery only in IN clause not in FROM.

I spend many hours to find a solution, and in my opinion, it's simply impossible using queryDSL to get sorted and grouped list and join it with another table in one query.

But maybe someone with grater experience has any idea, maybe a solution is simpler than I think

public List<SalesmanEntity> findSalesman(SalesmanSearchCriteriaTo criteria) {
	SalesmanEntity salesmanEntity = Alias.alias(SalesmanEntity.class);
	EntityPathBase<SalesmanEntity> alias = Alias.$(salesman);	
	JPAQuery<SalesmanEntity> query = new JPAQuery<SalesmanEntity>(getEntityManager()).from(alias);

	... a lot of wird IF's....

	if (criteria.getLastSoldItemTyp() != null) {
		OrderEntity order = Alias.alias(OrderEntity.class);
		EntityPathBase<OrderEntity> aliasOrder = Alias.$(order);
	
		query.join(aliasOrder)
		.on(Alias.$(salesman.getId()).eq(Alias.$(order.getSalesmanId())))
		.where(Alias.$(order.getItemTyp()).eq(criteria.getLastSoldItemTyp()));
	}
	
	return query.fetch();
}

Environment:

• Java 1.8

• SpringBoot 2.0.9

• QueryDSL 4.1.4

This is not a limitation of QueryDSL, rather it is a limitation of JPQL - the query language of JPA. For example, SQL does allow subqueries in the FROM clause, and as such querydsl-sql also allows it. With plain plain JPA, or even Hibernate's proprietary HQL it cannot be done. You would have to write a native SQL query then. For this you can have a look at @NamedNativeQuery.

It is possible to add subqueries on top of JPA using Common Table Expressions (CTE) using Blaze-Persistence. Blaze-Persistence ships with an optional QueryDSL integration as well.

Using that extension library, you can just write the following:

QRecursiveEntity recursiveEntity = new QRecursiveEntity("t");

List<RecursiveEntity> fetch = new BlazeJPAQuery<>(entityManager, cbf)
.select(recursiveEntity)
.from(select(recursiveEntity)
    .from(recursiveEntity)
    .where(recursiveEntity.parent.name.eq("root1"))
    .orderBy(recursiveEntity.name.asc())
    .limit(1L), recursiveEntity)
.fetch();

Alternatively, when using Hibernate, you can map a Subquery as an Entity, and then correlate that in your query. Using this you can achieve the same result, but you won't be able to reference any outer variables in the subquery, nor will you be able to parameterize the subquery. Both of these features will however be available with the above approach!

  @Entity
  @Subselect("SELECT salesman_id, sum(amount) FROM ( SELECT * FROM ORDERS ORDER BY ORDERS.date ) GROUP BY ORDERS.salesman_id")
  class OrdersBySalesMan {
        @Id @Column(name = "salesman_id") Long salesmanId;
        @Basic BigDecimal amount; // or something similarly
  }

And then in your query:

.from(QSalesman.salesman)
    .innerJoin(QOrdersBySalesMan.ordersBySalesMan)
    .on(ordersBySalesMan.salesmanId.eq(salesman.id))