TypedQuery with ManyToMany relations

Question

I have a problem to create query with TypedQuery interface, NamedQuery and many-to-many relationship. Here is my Report entity:

@Entity
@Table(name = "REPORT")
@NamedQueries({
    @NamedQuery(name = Report.NAMED_QUERY.FIND_USERS, query = "SELECT r.users FROM Report r WHERE r = :report")})

public class Report {

  public interface NAMED_QUERY {
      String FIND_USERS = "Report.findUsers";
  }

  @ManyToMany
  @JoinTable(name = "REPORT_USER", joinColumns = @JoinColumn(name = "REPORT_ID"), inverseJoinColumns = @JoinColumn(name = "USER_ID"))
  private Set<User> users;

  //another fields, getters and setters
}

And User Entity. Here i have no field that maps many-to-many relation.

@Entity
@Table(name = "USER")
public class User {

  //fields, getters and setters

}

I have no idea how to use this named query.

public List<User> findUsersRelatedToReport(Report report) {
    TypedQuery<User> query = entityManager.createNamedQuery(Report.NAMED_QUERY.FIND_USERS, User.class)
            .setParameter("report", report);
    return query.getResultList();
}

In the end I have exception:

Type specified for TypedQuery [package_name.User] is incompatible with query return type [interface java.util.Set]

Any help would be appreciated.

Answer 1

You cannot use collection valued attributes (in JPA specification terminology: collection_valued_path_expression) in SELECT.

That's why query is bit more complex, one way is following:

SELECT DISTINCT(u)
FROM User u 
WHERE EXISTS (
    SELECT r 
    FROM Report r 
    WHERE r = :report AND u MEMBER OF r.users)