CODEWITHSUNDEEP
javamysqljpqlquerydsl
fred
fred

Reputation: 1822

Translating JPQL to QueryDSL

I'm having trouble translating the following method to QueryDSL, would much appreciate some feedback!

javax.persistence.TypedQuery<Long> query = em().createQuery(
        "SELECT COUNT( DISTINCT b ) " +
                "FROM Project a " +
                "JOIN a.projectParticipants b " +
                "WHERE a.projectType IN :projectTypes " +
                "AND a.projectStatus = :projectStatus", Long.class
);
query.setParameter("projectTypes", filter.getProjectTypes());
query.setParameter("projectStatus", ProjectStatus.ACTIVE);
return query.getSingleResult();

The project class:

public class Project {

    @ManyToMany
    @JoinTable(name = "project_user", inverseJoinColumns = @JoinColumn(name = "user_id"))
    private Set<User> projectParticipants = new TreeSet<>();

}

The project_user table that represents the projects projectParticipation set:

CREATE TABLE IF NOT EXISTS `project_user` (
  `user_id` bigint(20) NOT NULL,
  `project_id` bigint(20) NOT NULL,
  PRIMARY KEY (`user_id`,`project_id`),
  KEY `project_user_project_id` (`project_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Upvotes: 1

Views: 138

Answers (1)

Timo Westk&#228;mper
Timo Westk&#228;mper

Reputation: 22190

The following should work

query.from(a)
  .join(a.projectParticipants, b)
  .where(a.projectType.in(projectTypes), a.projectStatus.eq(projectStatus))
  .singleResult(b.countDistinct());

Upvotes: 2

Related Questions