Problems join fetching with a where clause to same entity

Question

I have the following query in my repository:

SELECT p FROM Project p JOIN p.users u WHERE u.login =:login

There is a Many To Many relationshio between user and project. Everything works fine and it returns the user's projects, but I want it for each project to return the corresponding set of users. So updated it with a fetch join:

SELECT p FROM Project p JOIN FETCH p.users JOIN p.users u WHERE u.login =:login

But now i got the following exception:

nested exception is java.lang.IllegalArgumentException: Count query validation failed for method public abstract org.springframework.data.domain.Page com.example.app.repository.ProjectRepository.findAllByUserLo‌​gin(java.lang.String‌​,org.springframework‌​.data.domain.Pageabl‌​e)! org.hibernate.QueryException: query specified join fetching, but the owner of the fetched association was not present in the select list

Cannot find a workaround for it to execute the where clause and fetch the collection at the same time.

Project Entity:

@Entity
@Table(name = "project")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@Document(indexName = "project")
public class Project implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@NotNull
@Size(min = 10, max = 50)
@Column(name = "name", length = 50, nullable = false)
private String name;

@Size(max = 150)
@Column(name = "description", length = 150)
private String description;

@Column(name = "project_type")
private Integer projectType;

@Column(name = "is_active")
private Boolean isActive;

@Column(name = "date_created")
private ZonedDateTime dateCreated;

@Column(name = "date_updated")
private ZonedDateTime dateUpdated;

@ManyToMany
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@JoinTable(name = "project_user",
           joinColumns = @JoinColumn(name="projects_id", referencedColumnName="ID"),
           inverseJoinColumns = @JoinColumn(name="users_id", referencedColumnName="ID"))
private Set<User> users = new HashSet<>();

@OneToMany(mappedBy = "project")
@JsonIgnore
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
private Set<Milestone> milestones = new HashSet<>();

@OneToMany(mappedBy = "project")
@JsonIgnore
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
private Set<TaskList> taskLists = new HashSet<>();

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getName() {
    return name;
}

public void setName(String name) {
    this.name = name;
}

public String getDescription() {
    return description;
}

public void setDescription(String description) {
    this.description = description;
}

public Integer getProjectType() {
    return projectType;
}

public void setProjectType(Integer projectType) {
    this.projectType = projectType;
}

public Boolean isIsActive() {
    return isActive;
}

public void setIsActive(Boolean isActive) {
    this.isActive = isActive;
}

public ZonedDateTime getDateCreated() {
    return dateCreated;
}

public void setDateCreated(ZonedDateTime dateCreated) {
    this.dateCreated = dateCreated;
}

public ZonedDateTime getDateUpdated() {
    return dateUpdated;
}

public void setDateUpdated(ZonedDateTime dateUpdated) {
    this.dateUpdated = dateUpdated;
}

public Set<User> getOwners() {
    return users;
}

public void setOwners(Set<User> users) {
    this.users = users;
}

public Set<Milestone> getMilestones() {
    return milestones;
}

public void setMilestones(Set<Milestone> milestones) {
    this.milestones = milestones;
}

public Set<TaskList> getTaskLists() {
    return taskLists;
}

public void setTaskLists(Set<TaskList> taskLists) {
    this.taskLists = taskLists;
}

@Override
public boolean equals(Object o) {
    if (this == o) {
        return true;
    }
    if (o == null || getClass() != o.getClass()) {
        return false;
    }
    Project project = (Project) o;
    if(project.id == null || id == null) {
        return false;
    }
    return Objects.equals(id, project.id);
}

@Override
public int hashCode() {
    return Objects.hashCode(id);
}

@Override
public String toString() {
    return "Project{" +
        "id=" + id +
        ", name='" + name + "'" +
        ", description='" + description + "'" +
        ", projectType='" + projectType + "'" +
        ", isActive='" + isActive + "'" +
        ", dateCreated='" + dateCreated + "'" +
        ", dateUpdated='" + dateUpdated + "'" +
        '}';
    }
}

User Entity:

@Entity
@Table(name = "user")
@Cache(usage = CacheConcurrencyStrategy.NONSTRICT_READ_WRITE)
@Document(indexName = "user")
public class User extends AbstractAuditingEntity implements Serializable {

private static final long serialVersionUID = 1L;

@Id
@GeneratedValue(strategy = GenerationType.AUTO)
private Long id;

@NotNull
@Pattern(regexp = Constants.LOGIN_REGEX)
@Size(min = 1, max = 100)
@Column(length = 100, unique = true, nullable = false)
private String login;

@JsonIgnore
@NotNull
@Size(min = 60, max = 60) 
@Column(name = "password_hash",length = 60)
private String password;

@Size(max = 50)
@Column(name = "first_name", length = 50)
private String firstName;

@Size(max = 50)
@Column(name = "last_name", length = 50)
private String lastName;

@Email
@Size(max = 100)
@Column(length = 100, unique = true)
private String email;

@NotNull
@Column(nullable = false)
private boolean activated = false;

@Size(min = 2, max = 5)
@Column(name = "lang_key", length = 5)
private String langKey;

@Size(max = 20)
@Column(name = "activation_key", length = 20)
@JsonIgnore
private String activationKey;

@Size(max = 20)
@Column(name = "reset_key", length = 20)
private String resetKey;

@Column(name = "reset_date", nullable = true)
private ZonedDateTime resetDate = null;

@Column(name = "avatar", nullable = true)
private String avatar;

public Long getId() {
    return id;
}

public void setId(Long id) {
    this.id = id;
}

public String getLogin() {
    return login;
}

public void setLogin(String login) {
    this.login = login.toLowerCase(Locale.ENGLISH);
}

public String getAvatar() {
    return avatar;
}

public void setAvatar(String avatar) {
    this.avatar = avatar;
}

public String getPassword() {
    return password;
}

public void setPassword(String password) {
    this.password = password;
}

public String getFirstName() {
    return firstName;
}

public void setFirstName(String firstName) {
    this.firstName = firstName;
}

public String getLastName() {
    return lastName;
}

public void setLastName(String lastName) {
    this.lastName = lastName;
}

public String getEmail() {
    return email;
}

public void setEmail(String email) {
    this.email = email;
}

public boolean getActivated() {
    return activated;
}

public void setActivated(boolean activated) {
    this.activated = activated;
}

public String getActivationKey() {
    return activationKey;
}

public void setActivationKey(String activationKey) {
    this.activationKey = activationKey;
}

public String getResetKey() {
    return resetKey;
}

public void setResetKey(String resetKey) {
    this.resetKey = resetKey;
}

public ZonedDateTime getResetDate() {
   return resetDate;
}

public void setResetDate(ZonedDateTime resetDate) {
   this.resetDate = resetDate;
}

public String getLangKey() {
    return langKey;
}

public void setLangKey(String langKey) {
    this.langKey = langKey;
}

@Override
public boolean equals(Object o) {
    if (this == o) {
        return true;
    }
    if (o == null || getClass() != o.getClass()) {
        return false;
    }

    User user = (User) o;

    if (!login.equals(user.login)) {
        return false;
    }

    return true;
}

@Override
public int hashCode() {
    return login.hashCode();
}

@Override
public String toString() {
    return "User{" +
        "login='" + login + '\'' +
        ", avatar='" + avatar + '\'' +
        ", firstName='" + firstName + '\'' +
        ", lastName='" + lastName + '\'' +
        ", email='" + email + '\'' +
        ", activated='" + activated + '\'' +
        ", langKey='" + langKey + '\'' +
        ", activationKey='" + activationKey + '\'' +
        "}";
    }


}

Answer 1

Try to remove second join:

SELECT p FROM Project p JOIN FECTH p.users u WHERE u.login =:login

And if you want to get Projects which contains specified user by login then you can try this:

SELECT p FROM Project p JOIN FECTH p.users u WHERE :login in elements(u.login)