CODEWITHSUNDEEP
javaspringspring-data-jpa
Sotiris Oik
Sotiris Oik

Reputation: 133

Spring boot JPA filter data based on relation

I have 3 entities in my Project. Entity A (eA) has an One-to-Many relationship with Entity B (eB) and eB has an One-to-Many relationship with Entity C (eC). I have fetch = eager in all of them.

I want to get eA with eB and eC nested but i want to get specific entries from eC. I have created a simple query like this

SELECT eA.* from eA
INNER JOIN eB on eA.eB_id = eB.id
INNER JOIN eC on eB.eC_id = eC.id
WHERE eC.id IN (1,2,3)

It actually seems to work when I run this directly in the database but when I create function in my repository with this query it returns all data from eB and eC and the right ones based on the WHERE clause.

I have tried switching fetch to lazy but with no luck.

Do you have any idea how I can achieve this?

Thanks!

Upvotes: 3

Views: 1473

Answers (1)

E-Riz
E-Riz

Reputation: 32914

You should be able to use a derived query method in your repository interface, no SQL needed. Spring Data can derive queries based only on the name of a repository method and its parameter type(s). It's a powerful feature, useful to avoid the need to write SQL or JPQL queries manually. Here is another useful explanation with some examples.

For your case, the repository method name to filter nested objects would be somewhat complex, but doable. The exact repository method name depends on the field names in your entity classes, which you didn't show us. So assuming classes like this (annotations omitted):

public class A {
    private long id;
    private Collection<B> entityBs;
}

public class B {
    private long id;
    private Collection<C> entityCs;
}

public class C {
    private long id;
}

The repository method would look like this:

public interface EntityARepository extends JPARepository<A, long> {
    Collection<A> findByEntityBsEntityCsIdIn(long[] ids);
}

The return type of the repo method can be other types, as documented here, depending on how you want the results returned.

Upvotes: 1

Related Questions