Querydsl: How to write subquery for QueryDSL version 4.2.1?

[datntvn]

We are using QueryDSL version 4.2.1 on spring-boot project and we need to write subquery.

But all the example we found use SimpleSubQuery or JPASubQuery.

Neither of these 2 classes could be found in version 4.2.1.

Could you please suggest me how to write subquery for QueryDSL version 4.2.1?

[kartoffelsup]

What artifacts are you using? Are you using querydsl-sql or querydsl-jpa? querydsl-jpa definitely has JPASubQuery.

[thackel]

I think the best practice is:

JPQLQuery<Long> subQuery=JPAExpressions.select(QEntity.entity.id).where(somePredicate);
queryFactory.selectFrom(..something).where(QOtherEntity.otherEntity.id.in(subQuery);

[datntvn]

@kartoffelsup Thank you for your feedback. Please refer to above image.

I'm using VisualCode, with plugin for Java, but it does not show any suggest for those classes.

Other basic query like below is OK

JPAQueryFactory queryFactory = new JPAQueryFactory(em);
QCustomer customer = QCustomer.customer;
QCompany company = QCompany.company;
List<Tuple> result = queryFactory.select(
customer.name,
customer.id,
company.name,
company.id
)
.from(customer)
.innerJoin(customer.company, company)
.fetch();

This is my subquery which I am using and failed to compile:

SimpleSubQuery<Customer> mostPopularRaceSubquery = new JPASubQuery().from(customer).join(company).groupBy(company);

I'm using querydsl-jpa

[datntvn]

I think the best practice is:

JPQLQuery<Long> subQuery=JPAExpressions.select(QEntity.entity.id).where(somePredicate);
queryFactory.selectFrom(..something).where(QOtherEntity.otherEntity.id.in(subQuery);

Thank you @thackel for your reply.

From what you provided, I'm able to create the query, but in runtime, I got Exception:

The code is as below:

Below is the exception:

DEBUG com.querydsl.jpa.impl.JPAQuery - select customer from Customer customer where customer.company.id in (select company.id where company.id = ?1)
ERROR org.hibernate.hql.internal.ast.ErrorCounter - <AST>:0:0: unexpected end of subtree
ERROR org.hibernate.hql.internal.ast.ErrorCounter - <AST>:0:0: unexpected end of subtree
antlr.NoViableAltException: unexpected end of subtree
at org.hibernate.hql.internal.antlr.HqlSqlBaseWalker.fromElementList(HqlSqlBaseWalker.java:3600) [hibernate-core-5.2.17.Final.jar:5.2.17.Final]

I realized one thing, the subquery does not have "FROM" but I don't know how to fix

[thackel]

@datntvn you are missing the "from" part in the subquery. I missed it in my example.

JPAExpressions.select(QEntity.entity.id).from(QEntity.entity).where(somePredicate);

But this kind of simple query i would express by a join not a subselect.

[datntvn]

@thackel After added "FROM", the query seemed to work.

I tested the console's output SQL and it return data

But I get another runtime Exception as
nested exception is com.querydsl.core.NonUniqueResultException: javax.persistence.NonUniqueResultException: query did not return a unique result: 2] with root cause
javax.persistence.NonUniqueResultException: query did not return a unique result: 2
at org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:123) ~[hibernate-core-5.2.17.Final.jar:5.2.17.Final]

I think it's because the returned data is 2 records. How could I handle the queried result properly in this case?

Thank you so much for your help.

P/S: I prepared this simple query with QueryDSL in order for a more complicated scenario.

[datntvn]

@thackel I'm able to do it.

I replaced fetchOne with fetch

@kartoffelsup Although I am able to create subquery, please keep this as open because I still cannot use SimpleSubQuery and JPASubQuery for version 4.2.1

[thackel]

You are expecting just one result by adding .fetchOne(). Just use .fetch() and get a list of possible results.

[datntvn]

You are expecting just one result by adding .fetchOne(). Just use .fetch() and get a list of possible results.

I'm totally new to QueryDSL and I don't know what is what. I'm on the learning process.

[kartoffelsup]

@kartoffelsup Although I am able to create subquery, please keep this as open because I still cannot use SimpleSubQuery and JPASubQuery for version 4.2.1

Yes, you can't use them because they are not public api. Sorry, didn't see that before, just checked if the classes existed. The way that @thackel subscribed seems to be the proper way of doing subqueries in querydsl.

p.s. I'm not a querydsl maintainer so I can't close the issue anyway ;)

[jwgmeligmeyling]

@thackel already answered this. It is very intentional that JPASubQuery and SimpleSubQuery cannot be used anymore.