I have a ManyToMany relationship between "utilisateur" and "projet" and I want to extract all the users not existing in the project entity then this is my query:
Query req=utilisateurDAO.createQuery("select u from utilisateur u where not in(select p from projet p where p.utilisateurs.iduser=u.iduser) ");
and this is "Projet" entity:
@Entity
public class Projet implements Serializable {
@Column(name = "idprojet", nullable = false)
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
Integer idprojet;
@ManyToMany(mappedBy="projets", fetch = FetchType.LAZY)
java.util.List<com.gestion.projet.domain.Utilisateur> utilisateurs;
}
and this the "Utilisateur" Entity
@Entity
public class Utilisateur implements Serializable {
@Column(name = "iduser", nullable = false)
@Id
@GeneratedValue(strategy=GenerationType.IDENTITY)
Integer iduser;
@ManyToMany(fetch = FetchType.LAZY)
@JoinTable(schema = "public", name = "join_membre_projet", joinColumns = { @JoinColumn(name = "iduser", referencedColumnName = "iduser", nullable = false, updatable = false) }, inverseJoinColumns = { @JoinColumn(name = "idprojet", referencedColumnName = "idprojet", nullable = false, updatable = false) })
java.util.List<com.gestion.projet.domain.Projet> projets;
}
and i dont know why does not work?
HQL supports both sub selects as well as collection expressions.
All the users not existing in the project entity means all users who have no project assigned or users with an empty project list:
select u from Utilisateur where u.projets is empty