How to write subquery in querydsl?

Question

I have used in my project querydsl, hibernate and spring data jpa.I wrote this native query and working fine.but How can I write this query in Querydsl.

List<OpenChart> openChartList = (List<OpenChart>) getEntityManager()
.createNativeQuery( "select * from (select * from open_chart order by id desc ) open_chart where user_id="+userId+" group by patient_chart_id order by id",OpenChart.class).getResultList();

Solution 1

You need to use JPASubQuery

from the manual

2.1.13. Subqueries To create a subquery you create a JPASubQuery instance, define the query parameters via from, where etc and use unique or list to create a subquery, which is just a type-safe Querydsl expression for the query. unique is used for a unique (single) result and list for a list result.

 QEmployee employee = QEmployee.employee; 
 QEmployee e = new QEmployee("e"); 
 query.from(employee)
      .where(employee.weeklyhours.gt(
           new JPASubQuery().from(employee.department.employees, e)
                            .where(e.manager.eq(employee.manager))
                            .unique(e.weeklyhours.avg())
       )).list(employee); 

For Hibernate based sub query usage, use the HibernateSubQuery instead.

Solution 2

I prepared an example of how to use a subquery in the similar scenario to what you described, hope you can help and provide the basis for its implementation:

    SQLQuery sqlQuery = new SQLQuery(connection, PostgresTemplates.builder().quote().newLineToSingleSpace()
            .printSchema().build());

    ListSubQuery<Tuple> listSubQuery = new SQLSubQuery().from(QUsersPasswords.usersPasswords).orderBy(QUsersPasswords.usersPasswords.usuNummat.desc()).list(QUsersPasswords.usersPasswords.all());

    QUsersPasswords qSubquery = new QUsersPasswords("subquery");

    sqlQuery.from(listSubQuery.as("subquery")).limit(10);

    sqlQuery.list(qSubquery.all());