JPA criteria query in a many-to-many relationship using IN operator

I have the following two entities:

Profile, Category

In the Profile entity i have a List of categories that the profile has

In the Category entity i have a List of profiles that the category has

The generated table is called profiles_has_categories and has the following columns:

profile_id, category_id

I want to find all profiles that belong to any of the categories with the following ids 1, 2, 3, 4

I would do that in plain sql with:

SELECT p.* FROM profiles p, profiles_has_categories phc 
WHERE p.id = phc.profile_id 
AND phc.category_id IN (1, 2, 3, 4)

But i can't figure out how to do that using the CriteriaBuilder to construct a query in JPA:

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<Profile> criteria = cb.createQuery(Profile.class);
Root<Profile> root = criteria.from(Profile.class);

// Profile_.categories is a collection of Category objects
// categories is collection of Long (category ids) that i want to search in
// but is not working
criteria.where(root.get(Profile_.categories).in(categories))

List<Profile> results = em.createQuery(criteria).getResultList();

Answers


You don't want a get, you want a join:

criteria.where(root.join(Profile_.categories).in(categories))

Need Your Help

Cannot use javascript parameter with rails

javascript ruby-on-rails ruby-on-rails-3 ruby-on-rails-4 erb

I need to get the value of a parameter in a javascript code and pass it on to a rails partial view but it won't let me do that. Here is what I have:

MMO Server functionality in Unity3D

algorithm unity3d protocols multiplayer mmo

I've created a multiplayer server for Unity that works like this: