I have a many to many relationship between clients and users and am trying to run a query to return me all active users and their clients where the clients should also only be active. However it returns me all inactive clients too. My query looks like this
select u from User u inner join u.clientUsers cu join cu.client c where u.active = true and c.active = true
I am sure that my join type is incorrect. Can someone help please?
It will return all Users that are active and have an Active client. It is not returning clients, so when you access the user.clientUsers relationship, you get all associated clients. Returned entities are meant to reflect the state of the entity as it is in the database, so building an incomplete entity based on your query isn't supported in the specification. It would cause issues when tracking changes, and confusion when the real data is needed. Try instead basing your query on active clients with active users, and then access the the user from the client (assuming the relationships are bidirectional): "select c from Client c join c.clientUsers cu where c.active = true and cu.user.active = true" or if you want both in one query: "select c, u from Client c join c.clientUsers cu join cu.user u where c.active = true and u.active = true"
The second query will return a list of object[], with each object array containing a client and its associated active User. You can process it to map users and client collections if needed.