QueryDSL Predicate SetPath.any with multiple conditions

Question

I have a simple entity with one to many relationship

@Entity // and other @ stuff
public class Member {
  @Id
  private Long id;
  private String name;
  private List<Program> programs;
  ...
}

@Entity
public class Program {
   @Id
   private Long id;
   private Long programName;
   private ProgramType programType;
   private Long programCost;
   ...
}

Now using QueryDSL, I would like to query 'All members enrolled in a program with programType = "FULLTIME" and programCost > $1000'

I used the following predicate

Predicate predicate = QMember.member.programs.any()
    .programType.eq(ProgramType.FULLTIME)
      .and(QMember.member.programs.any().programCost.gt(1000));

with JPARepository

memberRepository.findAll(predicate);

Now the problem is that the two queries are independent. It returns al members with at least one program of type 'FULLTIME' or at least one program of cost greater than 1000.

Desired result : Return members if he has at least one program that is of type FULLTIME and cost > 1000.

score 19 · Accepted Answer · answered Jul 24 '16 at 08:54

19

Got some help here : https://groups.google.com/forum/#!topic/querydsl/hxdejLyqXos

Basically the conditions on the program need to be in a separate subQuery (a JPASubquery instance)

QProgram program = QProgram.program
JPASubQuery subQuery = new JPASubQuery();
subQuery.from(program)
        .where(program.programType.eq(ProgramType.FULLTIME),
            program.programCost.gt(1000));

Predicate predicate = QMember.member.name.eq("John")
    .and(subQuery.exists());

memberRepository.findAll(predicate);

answered Jul 24 '16 at 08:54

Pranjal

796
1
8
24

5

I think this example is outdated for querydsl 4.x – Shahbour Jul 12 '18 at 12:50
how to handle this in case of mongodb? jpa is for relational? – user1955934 Sep 02 '20 at 22:33
This solution misses the link between program and member. It should include an additional where clause in the subquery: program.in(QMember.member.programs) – Alex K. Oct 07 '21 at 19:43

score 5 · Answer 2 · answered Aug 09 '18 at 08:49

As mentionned by @Shahbour, this is not working anymore with QueryDsl 4.x+.

I had a similar case (except that my entities are bidirectionnal), and I've solved it with this :

QProgram program = QProgram.program;
QProgram member  = QProgram.member;

Predicate predicate = JPAExpressions
    .selectOne()
    .from(program)
    .where(program.member.id.eq(member.id),
            program.programCost.gt(1000),
            program.programType.eq(ProgramType.FULLTIME))
    )
    .exists();

memberRepository.findAll(predicate);

im confused with the first 2 lines of declaration, are you using a different example? QProgram does not have a member path? — user1955934, Sep 02 '20 at 22:16

QueryDSL Predicate SetPath.any with multiple conditions

2 Answers2