How to create subquery in Querydsl

Question

I am new to Querydsl. I have to convert following query into Querydsl. I tried like below but I am not getting result.

Can any one please tell me what I am missing or any thing doing wrong in query?

select * from room  as room 
      where room.nroom_id not in(
                          select rdm.nroom_id from roomdepartmentmapping as rdm)

I tried like this

    JPAQuery<Tuple> query = new JPAQuery<Tuple>(em);  

    query.from(room) 
         .where(room.nRoomId.notIn
                         (query.select(roomDepartmentMapping.nRoomId)
                               .from(roomDepartmentMapping)
                         )
               );

Console

   at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:31) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.core.types.OperationImpl.accept(OperationImpl.java:83) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.core.support.SerializerBase.handle(SerializerBase.java:92) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.jpa.JPQLSerializer.serialize(JPQLSerializer.java:220) ~[querydsl-jpa-4.1.4.jar:na]
        at com.querydsl.jpa.JPQLSerializer.visit(JPQLSerializer.java:358) ~[querydsl-jpa-4.1.4.jar:na]
        at com.querydsl.jpa.JPQLSerializer.visit(JPQLSerializer.java:39) ~[querydsl-jpa-4.1.4.jar:na]
        at com.querydsl.core.types.SubQueryExpressionImpl.accept(SubQueryExpressionImpl.java:57) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.core.support.SerializerBase.handle(SerializerBase.java:92) ~[querydsl-core-4.1.4.jar:na]            
        at com.querydsl.jpa.JPQLSerializer.visitOperation(JPQLSerializer.java:403) ~[querydsl-jpa-4.1.4.jar:na]
        at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:231) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:31) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.core.types.OperationImpl.accept(OperationImpl.java:83) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.core.support.SerializerBase.handle(SerializerBase.java:92) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.jpa.JPQLSerializer.serialize(JPQLSerializer.java:220) ~[querydsl-jpa-4.1.4.jar:na]
        at com.querydsl.jpa.JPQLSerializer.visit(JPQLSerializer.java:358) ~[querydsl-jpa-4.1.4.jar:na]
        at com.querydsl.jpa.JPQLSerializer.visit(JPQLSerializer.java:39) ~[querydsl-jpa-4.1.4.jar:na]
        at com.querydsl.core.types.SubQueryExpressionImpl.accept(SubQueryExpressionImpl.java:57) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.core.support.SerializerBase.handle(SerializerBase.java:92) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.core.support.SerializerBase.visitOperation(SerializerBase.java:270) ~[querydsl-core-4.1.4.jar:na]
        at com.querydsl.jpa.JPQLSerializer.visitOperation(JPQLSerializer.java:403) ~[querydsl-jpa-4.1.4.jar:na]
        at com.querydsl.core.support.SerializerBase.visit(SerializerBase.java:231) ~[querydsl-core-4.1.4.jar:na]           
        at com.querydsl.jpa.JPQLSerializer.visit(JPQLSerializer.java:39) ~[querydsl-jpa-4.1.4.jar:na]
        at com.querydsl.core.types.SubQueryExpressionImpl.accept(SubQueryExpressionImpl.java:57) ~[querydsl-core-4.1.4.jar:na]

Answer 1

Rather than using the instance of the enclosing query, an instance of JPASubQuery is required. You can use new JPASubQuery() or the convenience method JPAExpressions.select. Your query should look something like this:

JPAQuery<Tuple> query = new JPAQuery<Tuple>(em);  

query.from(room) 
     .where(room.nRoomId.notIn
                     (JPAExpressions.select(roomDepartmentMapping.nRoomId)
                           .from(roomDepartmentMapping)
                     )
           );