Assuming I have the following mapping (code reduced to focus on problem)
public class User {
@ManyToMany
private List<Product> products;
}
+----+---------+-----------+--------+
| id | user_id | product_id| status |
+----+---------+-----------+--------+
| 1 | 1 | 1 | 1 |
| 2 | 1 | 2 | 0 |
+----+---------+-----------+--------+
public class UserProduct {
@ManyToOne
private User user;
@ManyToOne
private Product product;
private boolean status;
}
How can I limit the products I get based on the status column in the join table?
I'm currently trying to figure out a JPQL syntax (assuming you have to explicitly make a new query) like the following (won't work)
"SELECT u FROM Users u, UserProduct up WHERE u.id = :id AND up.status = :status"
Note: Using Hibernate as the Persistence Provider, though I wouldn't want to limit myself.