How to do a search with multiple ElementCollections

Question

Given the following entity:

@Entity
@Table(name = "subscription")
public class Subscription implements Serializable {

  private static final long serialVersionUID = 1L;

  @ElementCollection
  @CollectionTable(joinColumns= @JoinColumn(name="subscription"))
  private Set<Code> mainCodes = new HashSet<>();

  @ElementCollection
  @CollectionTable(joinColumns= @JoinColumn(name="subscription"))
  private Set<Code> otherCodes = new HashSet<>();

}

So a Subscription can have zero or more mainCodes or otherCodes it's interested in. I can get hold of the mainCode and otherCode of a certain object that passes by. The codes themselves are embeddables with only single String fields.

How do I create a JPA Query (or CriteriaBuilder) which searches in these collections with an "OR" mechanism?

So basically I'm looking for a query like this:

select s from subscription s where :myMainCode IN s.mainCodes OR :otherCode IN s.otherCodes

Is something like this doable with CriteriaBuilder or do I need to use a more explicit query? If so, what does the query look like?

EDIT: Tried this with CriteriaBuilder:

final CriteriaBuilder cb = this.entityManager.getCriteriaBuilder();
final CriteriaQuery<Subscription> cq = cb.createQuery(Subscription.class);
final Root<Subscription> root = cq.from(Subscription.class);

final Expression<Collection<Code>> mainCodes = root.get("mainCodes");
final Predicate containsMainCode = cb.isMember(obj.getClassCode(), mainCodes);

final Expression<Collection<Code>> otherCodes = root.get("otherCodes");
final Predicate containsOtherCode = cb.isMember(obj.getOtherCode(), otherCodes);

final Predicate searchPredicate = cb.or(containsMainCode, containsOtherCode);
cq.select(root).where(searchPredicate);

However, this creates an inner join of both collections involved, meaning that it will return no results if there is a row for mainCode, but not for otherCode in the database, it generates this query:

SELECT t0.ID FROM Subscription_OTHERCODES t2, Subscription_MAINCODES t1, subscription t0 WHERE ((t1.CODESYSTEM = ?) AND (t1.CODE = ?)) OR ((t2.CODESYSTEM = ?) AND (t2.CODE = ?))) AND ((t1.subscription = t0.ID) AND (t2.subscription = t0.ID))

So even if it finds a matching mainCode, it fails if it doesn't have any otherCode.

What's wrong with the query you have? – beerbajay Apr 14 '15 at 12:41 — beerbajay, Apr 14 '15 at 12:41

Zielu · Answer 1 · 2015-04-15T10:33:32.397

0

It is other way around that in your example. For example if the code has name property):

select s from Subscription s left join s.mainCodes m left join s.otherCodes o 
where m.name IN :myMainCode or o.name IN :myOtherCode

edited Apr 15 '15 at 10:33

answered Apr 14 '15 at 14:14

Zielu

8,312
4
28
41

use left joint as in the edit. I am not using criteria but there is equivalent construction. – Zielu Apr 15 '15 at 10:33

How to do a search with multiple ElementCollections

1 Answers1