How to select a JPA Entity by whitelisting a property of its child Entity with the Criteria API?

Question

I'm stuck with the following hypothetical problem:

Using the Criteria API (and not JPQL), and given

A table full of users, each one having multiple cars

@Entity 
public class User {
    @Id 
    private Long     id;

    @OneToMany 
    private Set<Car> cars;
}

@Entity 
public class Car {
    @Id 
    private Long id;

    private String model;
}

A Set<String> containing car models:

Set<String> models = getThousandsOfModels();

How can I select the Users having at least one Car whose model is in the models Set ?

I've read many SO answers, tried different ways (the most promising seemed to involve creating predicates with Expression<Collection<String>>, using .in() etc.) but nothing worked.

Why the close-vote ? No other data is necessary to understand *the problem*. The only thing missing here is *the solution*, that I don't have. I don't need code, just put me in the right direction if you know the answer. — Andrea Ligios, Oct 23 '15 at 12:35
Where it goes wrong is probably the "nothing worked" bit, that comes dangerously close to "it doesn't work". I would illustrate what you tried in code instead of a distant description, to me an "in" clause is indeed the way to go. — Gimby, Oct 23 '15 at 12:45
Start writing it in JPQL. You'll realize you need a join. So you need the same thing when using Criteria: `select distinct u from User u join u.cars c where c.model in :models`. Note that Oracle, for example, has a limit of 1000 elements in an `in` clause. — JB Nizet, Oct 23 '15 at 12:56
@JBNizet I was trying to avoid that (after having read an SO answer that avoided the Join in a similar case) but the more I think of it the more I guess it's the only option. I'll limit that to 1000 then... thank you, feel free to post it as an answer so I can upvote it — Andrea Ligios, Oct 23 '15 at 13:03
That comment would not be a good enough answer, and I don't feel like fighting with the ugly criteria API to create one, sorry. There's no way to add a restriction on a column of a table without having that table in the from clause. You NEED a join. — JB Nizet, Oct 23 '15 at 13:06
@JBNizet Ok. I disagree on the comment not being good enough to be posted as an answer, though :) (for the records, [this](http://stackoverflow.com/a/9325205/1654265) is the answer I was referring to, that BTW handles a case different from mine). — Andrea Ligios, Oct 23 '15 at 13:12
@JBNizet it worked like a charm. I just need to benchmark it now, but since it's the only way, who cares. Still thanks — Andrea Ligios, Oct 23 '15 at 13:35

score 1 · Accepted Answer · answered Oct 23 '15 at 14:52

The following code did the trick:

Set<String> models = getHundredsOfModels(); 
if (models.size()>1000) throw new TooManyResultsException(); // Oracle limit

CriteriaBuilder cb          = em.getCriteriaBuilder();
CriteriaQuery<User> cQuery  = cb.createQuery(User.class);
Root<User> user             = cQuery.from(User.class);
List<Predicate> predicates  = new ArrayList<Predicate>();

//  This lines are the trick  ------------------------------------------
Join<User, Car> usersCars   = user.join("cars", JoinType.INNER);
Predicate p                 = usersCars.<String>get("model").in(models);
// ---------------------------------------------------------------------    

predicates.add(p);
cQuery.select(user).where(predicates.toArray(new Predicate[predicates.size()]));

How to select a JPA Entity by whitelisting a property of its child Entity with the Criteria API?

1 Answers1