Hibernate select elements of collection with a given property

Question

Entity Project has a collection property contributors mapped with a @OneToMany relationship to entity User

@Entity
@Table( name = "projects" )
public class Project {
    ...

    @OneToMany
    @JoinTable(name = "project_contributors")
    private List<User> contributors = new ArrayList<User>();

    ...
}

I then need to check if contributors already has a user with id contributorId before adding it. I am trying with HQL query, but I am clearly quite innept.

What I am trying:

Query query = session.createQuery(
        "select p.contributors from Project p where p.id = :pId and p.contributors.id = :cId"
    );

query.setParameter("pId", projectId);
query.setParameter("cId", contributorId);

@SuppressWarnings("unchecked")
List<User> res = (List<User>) query.list();

But it gives the error

illegal attempt to dereference collection [project0_.id.contributors] with element property reference [id]

Is there a good samaritan that would like to give me a little push?

another try I made is

"select p.contributors as c from Project p where p.id = :pId and c.id = :cId"

but nothing.

Answer 1

contributors is a Collection. As such, it does not have an attribute named id.

Id is an attribute of the elements of this Collection.

You can fix the issue by joining the collection instead of dereferencing it:

SELECT p 
  FROM Project pj 
  JOIN pj.contributors  p 
 WHERE pj.id       = :pId
   AND p.Id     = :cId

Answer 2

It helps to read your HQL as if it was Java code trying to dereference fields in Java classes:

p.contributors.id

You're trying to access the id of p.contributors, which is a List<User>. A List doesn't have an ID. So that can't work.

select p.contributors

Such a query, if it were correct, would return a list of list of contributors. That's not what you want either.

How would you do it in SQL? With a join. Same with JPQL:

select c from Project p
join p.contributors c
where p.id = :pId and c.id = :cId

Doing a join and assigning it an alias allows querying the target entity of the association.