Undeclared path on QueryDSL

Question

My QueryDSL gives me the 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'm trying the code in QueryDSL the following query that could not be done 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 full query:

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

I then tried to put the qRolloutAdmin in the query.from(qRolloutAdmin) as:

query.from(qRolloutAdmin).innerJoin(qRolloutAdmin.rollout, qRollout).innerJoin(qRolloutAdmin.admin, qAdmin).innerJoin(qBTS.rollout, qRollout);

It seemed to improve things a bit and this time the exception would be nearly the same but on the bts one:

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 removed the inner join on the bts to have it in the builder:

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

Apart from trying to solve the issue, I have questions arising:

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'm running QueryDSL 3.5.0

EDIT: I then suspected the parameters order on the innerJoin might have sense, and so I tried this, picturing in a left to right fashion:

query.from(qRollout);
query.innerJoin(qRollout, qRolloutAdmin.rollout);
query.innerJoin(qRolloutAdmin.admin, qAdmin);
query.innerJoin(qRollout, qBTS.rollout);

Which gave a different 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

I then tried multiple entities in the from() method:

query.from(qRollout, qRolloutAdmin, qBTS);

but the exception remained the same.

EDIT2: I tried specifying the innerJoin with a on() method as described in the 2.1.8. General usage section of the reference documentation:

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

And got the exception:

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: I added an on() method on each of the innerJoin methods:

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

But it still complains of an undeclared path for 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: I tried the following:

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

It still gives me an exception though:

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

A work around the joining issue was to remove the inner join statements and replace them with and clauses as in:

builder.and(qRolloutAdmin.rollout.id.eq(qRollout.id)).and(qRolloutAdmin.admin.id.eq(qAdmin.id)).and(qBTS.rollout.id.eq(qRollout.id));

EDIT: The issue has been solved at:

QueryDSL Could not determine data type for searched case statement

score 7 · Answer 1 · answered Oct 26 '14 at 08:21

7

Joins in JPA queries are more about property traversal than SQL joins. So you will need to rewrite your query to make sure that all paths are connected via properties to the root variable.

If you want to start from RolloutAdmin then

query.from(qRolloutAdmin)
     .innerJoin(qRolloutAdmin.rollout, qRollout)
     .innerJoin(qRolloutAdmin.admin, qAdmin);

Now you can use qRolloutAdmin, qRollout and qAdmin in your query. qBTS is not yet connected to the property tree.

Does the entity sitting in the from method have to be a child one ?

It needs to be a root variable, no child.

Is there any difference between doing an innerJoin and an equal in the builder ?

The resulting SQL is different and might be differently optimized. Using joins is the preferred way.

answered Oct 26 '14 at 08:21

Timo Westkämper

21,824
5
78
111

May I ask what do we mean with a root path / variable ? – Stephane Oct 26 '14 at 08:26
I may have updated my question after your comment. My qBTS should be connected with the innerJoin on it. – Stephane Oct 26 '14 at 08:28
I wonder why I cannot start from Rollout. This: query.from(qRollout) .innerJoin(qRolloutAdmin.rollout, qRollout) .innerJoin(qRolloutAdmin.admin, qAdmin); complains of an undeclared path 'rolloutAdmin'. – Stephane Oct 26 '14 at 08:56
I'm searching the doc of the 3.5.0/reference to try understand the logic of path handling... there is a bit on it ? – Stephane Oct 26 '14 at 08:57
root means that it is not a child, root paths are variable and child paths are usually properties. – Timo Westkämper Oct 26 '14 at 19:49
1

The reference docs assume maybe too much that JPQL joins are already familiar. Here is it in a nutshell: introduce top level (root) variables either via from or alias usage (e.g. join(property, alias)), every other variable usage is illegal. – Timo Westkämper Oct 26 '14 at 19:51
If you want to use rolloutAdmin somewhere introduce it via from or a join alias. – Timo Westkämper Oct 26 '14 at 19:52
So the join alias makes the entity available as a root to another join... Does this explains the InvalidWithClauseException I have ? – Stephane Oct 26 '14 at 23:28
Hi Timo, it seems the issue is with Hibernate https://hibernate.atlassian.net/browse/HHH-2772 But maybe the QueryDSL API offers the mentioned work around as well ? – Stephane Oct 27 '14 at 19:28
I could work around the join on Hibernate issue by using a builder.and(qRolloutAdmin.rollout.id.eq(qRollout.id)).and(qRolloutAdmin.admin.id.eq(qAdmin.id)).and(qBTS.rollout.id.eq(qRollout.id)); instead of the join on statements. – Stephane Oct 28 '14 at 06:46
I also had to wrap the constructor parameters in NumberTemplate.create() statements otherwise I would get a: org.hibernate.QueryException: Parameters are only supported in SELECT clauses when used as part of a INSERT INTO DML statement This thread helped me: http://stackoverflow.com/questions/26234067/how-to-select-literals-in-querydsl – Stephane Oct 28 '14 at 06:51
To stay within the abstraction and not use any column name I tried a: new CaseBuilder().when(qBTS.status.eq(com.nsn.nitro.project.data.utils.BTSStatus.PLA‌NNED)) but it produced: case when bts3_.status=COMPLETED with the quotes missing. Same for the construct: qBTS.status.when(com.nsn.nitro.project.data.utils.BTSStatus.PLANNED) which produced: case bts3_.status when COMPLETED still without quotes. – Stephane Oct 28 '14 at 07:41
I've seen EnumExpression-s are supported but I could not see in the reference documentation how to use them. – Stephane Oct 28 '14 at 08:22
The issue has been solved at http://stackoverflow.com/questions/26648491/querydsl-could-not-determine-data-type-for-searched-case-statement It was Hibernate which was not cutting it. – Stephane Nov 28 '14 at 15:29

Undeclared path on QueryDSL

1 Answers1