Search by nested property of collection field with criteria api

Question

I'm trying to find all entities that have some nested elements and nested elemens have collections of elements, and I need to find it by property of those collections.

It would be something like this

class A{
    private B b;
}

class B{
   private Collection<C> cCol;
}

class C{
   private String name;
}

So I want to get all A elements that have B elements that have a C which name matches given parameter.

Not sure how to do it with JPA Critieria API. I know there is in predicate, or MEMEBER OF in JPQL but I need to search by property of element in collection, not a collection member.

Tried things like root.get(a.b.c.name) and also with root.fetch(a.b) or root.fetch(b.c) but always ended up with some exceptions about illegal api usage

score 12 · Accepted Answer · answered Nov 07 '16 at 13:28

I want to get all A elements that have B elements that have a C which name matches given parameter.

When trying to navigate the criteria API I find it immensely helpful to write the JPQL query first. Here it is:

SELECT a
FROM A a
WHERE EXISTS(
    SELECT c FROM a.b b JOIN b.cCol c WHERE c.name = 'condition'
)

Now the criteria API becomes clearer (if that is possible at all):

CriteriaBuilder cb = em.getCriteriaBuilder();
CriteriaQuery<A> aQuery = cb.createQuery(A.class);
Root<A> a = aQuery.from(A.class);

Subquery<C> cSubquery = aQuery.subquery(C.class);
Root<A> aSubroot = cSubquery.correlate(a);
Join<A, B> b = aSubroot.join("b");  // "b" is the name of the property of A that points to B
Join<B, C> c = b.join("cCol"); // "cCol" is the name of the property of C that holds the related C objects

cSubquery.select(c);
cSubquery.where(cb.equal(c.get("name"), "XXXXXXX"));

aQuery.where(cb.exists(cSubquery));

TypedQuery<A> aTypedQuery = em.createQuery(aQuery);
aTypedQuery.getResultList();

The names of the Java variables are the same as in the JPQL, e.g. Join<A, B> b corresponds to the JPQL FROM a.b b.

`aQuery.distinct(true).where(cb.equal(root.get("b").get("cCol").get("name"), searchString))` should also give the same results without the need for a subquery - the equivalent JPQL query would be `SELECT DISTINCT a from A a JOIN a.b b JOIN b.cCol c WHERE c.name = :searchString` — mata, Nov 07 '16 at 15:21
The reason I prefer subqueries over DISTINCT JOIN is performance. Although I am not an expert in this, here are some resources supporting it: [(1)](https://danmartensen.svbtle.com/sql-performance-of-join-and-where-exists), [(2)](http://www.toadworld.com/platforms/oracle/w/wiki/4773.exists-versus-distinct), [(3)](http://stackoverflow.com/questions/13692992/join-versus-exists-performance), [(4)](http://stackoverflow.com/questions/12201885/how-to-determine-what-is-more-effective-distinct-or-where-exists). The actual result and performance gain depends on the exact use case of course. — Nikos Paraskevopoulos, Nov 07 '16 at 15:36

score 5 · Answer 2 · edited May 23 '17 at 12:33

5

The below should work

root.get("a").get("b").get("name")

See

How to create specification using JpaSpecificationExecutor by combining tables?

edited May 23 '17 at 12:33

Community

1
1

answered Nov 07 '16 at 09:26

Essex Boy

7,565
2
21
24

2

From the used field names it should be `root.get("b").get("cCol").get("name")` (assuming `root` is `Root`). – mata Nov 07 '16 at 09:38
4

Unfortunatelly this fails with IllegalStateException: Illegal attempt to dereference path source [null.b.cCol] of basic type – Nadir Nov 07 '16 at 10:39
@Nadir - you should probably post more code on how you try to create the query – mata Nov 07 '16 at 15:22
I've been trying to write a test for this without success because the get("cCol") is actually a collection. – Essex Boy Nov 07 '16 at 15:46

score 0 · Answer 3 · edited May 22 '21 at 03:14

0

I was successful by using

root.**join**("a").get("b").get("name"):

because it's a collection.

edited May 22 '21 at 03:14

rlandster

7,294
14
58
96

answered May 21 '21 at 16:39

user1958206

1

score 0 · Answer 4 · answered Aug 03 '21 at 13:32

0

I did it by using joins like this:

builder = entityManager.getCriteriaBuilder();
CriteriaQuery<A> query = builder.createQuery(A.class);
Root<A> root = query.from(A.class);
root.join("b").join("c").get("name");

answered Aug 03 '21 at 13:32

M at

1,020
1
12
26

Search by nested property of collection field with criteria api

4 Answers4