My QueryDSL gives me an exception:
2014-10-26 02:12:00,013 DEBUG [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: Undeclared path 'rolloutAdmin'. Add this path as a source to the query to be able to reference it.; nested exception is java.lang.IllegalArgumentException: Undeclared path 'rolloutAdmin'. Add this path as a source to the query to be able to reference it.
I am trying to execute code in QueryDSL with the following query, which cannot be executed in JPQL:
@Query("SELECT new com.nsn.nitro.project.data.jpa.domain.RolloutMeta(r, count(b.id) as btsNbAll, ifnull(sum(b.status = com.nsn.nitro.project.data.utils.BTSStatus.PLANNED), 0) as btsNbPlanned, ifnull(sum(b.status = com.nsn.nitro.project.data.utils.BTSStatus.COMPLETED), 0) as btsNbCompleted, ifnull(sum(b.status = com.nsn.nitro.project.data.utils.BTSStatus.COMPLETED), 0) * 100.0 / count(b.id) as btsNbPercentage) FROM Rollout r, RolloutAdmin ra, BTS b WHERE b.rollout.id = r.id AND r.id = ra.rollout.id AND ra.admin = :admin GROUP BY r.id")
public Page<RolloutMeta> findMetaByAdmin(@Param("admin") Admin admin, Pageable page);
Here is the complete request:
@Override
@Transactional(readOnly = true)
public Page<RolloutMeta> findMetaByAdmin(Admin admin, Pageable page) {
JPAQuery query = new JPAQuery(rolloutRepository.getEntityManager());
QRollout qRollout = QRollout.rollout;
QRolloutAdmin qRolloutAdmin = QRolloutAdmin.rolloutAdmin;
QAdmin qAdmin = QAdmin.admin;
QBTS qBTS = QBTS.bTS;
query.from(qRollout).innerJoin(qRolloutAdmin.rollout, qRollout).innerJoin(qRolloutAdmin.admin, qAdmin).innerJoin(qBTS.rollout, qRollout);
BooleanBuilder builder = new BooleanBuilder();
builder.and(qAdmin.eq(admin));
query.where(builder)
NumberExpression<Integer> statusPlanned = qBTS.status.when(com.nsn.nitro.project.data.utils.BTSStatus.PLANNED).then(new Integer(1)).otherwise(new Integer(0));
NumberExpression<Integer> statusCompleted = qBTS.status.when(com.nsn.nitro.project.data.utils.BTSStatus.COMPLETED).then(new Integer(1)).otherwise(new Integer(0));
NumberExpression<Integer> btsNbPlanned = statusPlanned.sum();
NumberExpression<Integer> btsNbCompleted = statusCompleted.sum();
NumberExpression<Integer> btsPercentage = statusCompleted.sum().divide(new Integer(100)).multiply(qBTS.count());
query.orderBy(btsPercentage.desc());
QRolloutMeta qRolloutMeta = new QRolloutMeta(qRollout, qBTS.count(), btsNbPlanned, btsNbCompleted, btsPercentage);
List<RolloutMeta> resultList = query.list(qRolloutMeta);
long total = resultList.size();
query.offset(page.getOffset());
query.limit(page.getPageSize());
resultList = query.list(qRolloutMeta);
Page<RolloutMeta> rolloutMetas = new PageImpl<RolloutMeta>(resultList, page, total);
return rolloutMetas;
}
Then I tried putting qRolloutAdmin in query.from (qRolloutAdmin) as:
query.from(qRolloutAdmin).innerJoin(qRolloutAdmin.rollout, qRollout).innerJoin(qRolloutAdmin.admin, qAdmin).innerJoin(qBTS.rollout, qRollout);
It seemed to improve a bit, and this time the exception would be pretty much the same, but on bts:
2014-10-26 08:51:18,489 DEBUG [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: Undeclared path 'bTS'. Add this path as a source to the query to be able to reference it.; nested exception is java.lang.IllegalArgumentException: Undeclared path 'bTS'. Add this path as a source to the query to be able to reference it.
And so I deleted the inner join on bts to have it in the linker:
query.from(qRolloutAdmin).innerJoin(qRolloutAdmin.rollout, qRollout).innerJoin(qRolloutAdmin.admin, qAdmin);
BooleanBuilder builder = new BooleanBuilder();
builder.and(qBTS.rollout.eq(qRollout)).and(qAdmin.eq(admin));
query.where(builder);
But it still gives the same previous exception:
2014-10-26 09:08:00,397 DEBUG [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: Undeclared path 'bTS'. Add this path as a source to the query to be able to reference it.; nested exception is java.lang.IllegalArgumentException: Undeclared path 'bTS'. Add this path as a source to the query to be able to reference it.
In addition to trying to solve the problem, I have questions:
1- Does the entity sitting in the from method have to be a child one ?
2- Is there any difference between doing an innerJoin and an equal in the builder ?
I am running QueryDSL 3.5.0
EDIT: I then suspected that the order of the parameters on innerJoin might make sense, and so I tried this, portraying from left to right:
query.from(qRollout);
query.innerJoin(qRollout, qRolloutAdmin.rollout);
query.innerJoin(qRolloutAdmin.admin, qAdmin);
query.innerJoin(qRollout, qBTS.rollout);
Which gave another exception this time:
2014-10-26 10:02:04,354 DEBUG [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: rolloutAdmin.rollout is not a root path; nested exception is java.lang.IllegalArgumentException: rolloutAdmin.rollout is not a root path
Then I tried several objects in the from () method:
query.from(qRollout, qRolloutAdmin, qBTS);
but the exception has not changed.
EDIT2: innerJoin on(), 2.1.8. :
query.from(qRollout);
query.innerJoin(qRolloutAdmin).on(qRolloutAdmin.rollout.eq(qRollout));
query.innerJoin(qRolloutAdmin).on(qRolloutAdmin.admin.eq(qAdmin));
query.innerJoin(qRollout).on(qBTS.rollout.eq(qRollout));
:
2014-10-26 10:24:11,098 DEBUG [ExceptionsHandler] org.springframework.dao.InvalidDataAccessApiUsageException: rolloutAdmin is already used; nested exception is java.lang.IllegalStateException: rolloutAdmin is already used
EDIT3: on() innerJoin:
QRolloutAdmin qRolloutAdmin = QRolloutAdmin.rolloutAdmin;
QRollout qRollout = QRollout.rollout;
QAdmin qAdmin = QAdmin.admin;
QBTS qBTS = QBTS.bTS;
query.from(qRollout);
query.innerJoin(qRolloutAdmin.rollout).on(qRolloutAdmin.rollout.eq(qRollout));
query.innerJoin(qRolloutAdmin.admin).on(qRolloutAdmin.admin.eq(qAdmin));
query.innerJoin(qBTS.rollout).on(qBTS.rollout.eq(qRollout));
rolloutAdmin:
Caused by: java.lang.IllegalArgumentException: Undeclared path 'rolloutAdmin'. Add this path as a source to the query to be able to reference it.
EDIT4: :
query.from(qRollout, qRolloutAdmin, qAdmin, qBTS);
query.innerJoin(qRolloutAdmin.rollout).on(qRolloutAdmin.rollout.eq(qRollout));
query.innerJoin(qRolloutAdmin.admin).on(qRolloutAdmin.admin.eq(qAdmin));
query.innerJoin(qBTS.rollout).on(qBTS.rollout.eq(qRollout));
:
Caused by: java.lang.IllegalArgumentException: org.hibernate.hql.internal.ast.InvalidWithClauseException: with clause can only reference columns in the driving table [select rollout, count(bTS), sum(case when bTS.status = ?1 then ?2 else ?3 end), sum(case when bTS.status = ?4 then ?2 else ?3 end), (sum(case when bTS.status = ?4 then ?2 else ?3 end) / ?5) * count(bTS)
from com.nsn.nitro.project.data.jpa.domain.Rollout rollout, com.nsn.nitro.project.data.jpa.domain.RolloutAdmin rolloutAdmin, com.nsn.nitro.project.data.jpa.domain.Admin admin, com.nsn.nitro.project.data.jpa.domain.BTS bTS
, :
builder.and(qRolloutAdmin.rollout.id.eq(qRollout.id)).and(qRolloutAdmin.admin.id.eq(qAdmin.id)).and(qBTS.rollout.id.eq(qRollout.id));
EDIT: :
QueryDSL case