CODEWITHSUNDEEP
javajpacriteria-api
Jogi
Jogi

Reputation: 295

query that should return entities qith specific related entities

Generally my questian is very simple I think, nevertheless I couldn't find a good solution. Let's say I have an Entity class called MyEntity which has a OneToMany relation to an Entity class called EntityAttribute, so it has a list or set attributes with objects of this class. EntityAttribute has an attribute name of type String.

Now I want to implement a method which takes attribute names and returns all entities that contains for each name in attributes at least one attribute with that name. Although this sounds very straight forward, the only solution I found was to execute a query for each attribute name and merge the results like this:

for (String name : attributeNames) {
  CriteriaQuery<MyEntity> cq = cb.createQuery(MyEntity.class);
  Root<MyEntity> entity = cq.from(MyEntity.class);
  Join<MyEntity, EntityAttribute> attributeJoin = entity.join(MyEntity_.attributes);
  cq.where(attributeJoin.get(EntityAttribute_.name).equals(name));
  cq.select(entity);
  ... // get result list and merge
  }

This code isn't tested but generally is one solution. This doesn't seem to be the most efficient one. Another solution I testet was to use multiple joins like

CriteriaQuery<MyEntity> cq = cb.createQuery(MyEntity.class);
Root<MyEntity> entity = cq.from(MyEntity.class);
List<Predicate> predicates = new ArrayList<>();
for (String name : attributeNames) {
  Join<MyEntity, EntityAttribute> attributeJoin = entity.join(MyEntity_.attributes);
  predicates.add(attributeJoin.get(EntityAttribute_.name).equals(name));
}
cq.select(predicates.toArray(new Predicate[] {}));
... // get result list

This seems to be more efficient, but it iterates over the cartesian products... So it's highly inefficient.

I could also imagine to nest subqueries, but this seems to be very complicated.

The question simply is: What is the best solution for this problem? Afterwards I would also like to implement AND and OR, so I can query for all entities with attributes x and (y or z) or something like that. But for now I only want to make the AND case.
Thanks in advance

Upvotes: 1

Views: 44

Answers (1)

David Levesque
David Levesque

Reputation: 22451

Maybe you could achieve this using in clause + group by + having + count, if I understand your question correctly. The idea is to count the number of matches for each MyEntity. If the count is equal to the number of attributes passed in, it means that each of them was found for that entity (assuming they are unique). In JPQL the query would look like this:

select e from MyEntity e join e.attributes a
where a.name in (:attributeNames)
group by e having count(*) = :attributeCount

where :attributeCount is the value of attributeNames.size().

I'm not very familiar with the criteria API, but you can experiment with something like this:

...
cq.groupBy(entity);
cq.having(cb.equal(cb.count(entity), attributeNames.size()));
// TODO: add IN clause
...

Upvotes: 1

Related Questions