CODEWITHSUNDEEP
javaspring-data-jpa
Robus
Robus

Reputation: 8259

Spring Data JPA - filtering @OneToMany relation separately

Suppose the following:

public class Building {
   @Column(nullable = false)
   private String zipCode;

   @OneToMany(mappedBy = "building", fetch = FetchType.LAZY)
   private final Set<Contract> contracts = new HashSet<>();
}

public class Contract {
   @ManyToOne(optional = false, fetch = FetchType.EAGER)
   @JoinColumn(nullable = false, updatable = false)
   private Building building;


   @Column(nullable = false)
   private LocalDate activeFrom;
}

Using preferably spring-data-jpa repositories, how can I select all buildings with a certain zipCode, and then for each building, all contracts with an activeFrom earlier than X?

All solutions I can find on the internet seem to focus on filtering the primary object (Building), whereas I'd like to use different dynamic criteria for the child (Contract) and receive an empty list if none are found. I explicitly want to receive Buildings with no matching Contracts.

It is fine if a DTO/projection is used rather than the entity classes.

Upvotes: 1

Views: 876

Answers (2)

Stoil Terziev
Stoil Terziev

Reputation: 109

You can use something like this to filter by zipCode and activeFrom

String FIND_ALL_BUILDINGS_BY_ZIP_CODE_AND_ACTIVE_FORM = "SELECT b FROM Building b" +
        " inner join b.contract as c " +
        " with c.activeFrom > :date " +
        " where b.zipCode = :zipCode";

@Query(FIND_ALL_BUILDINGS_BY_ZIP_CODE_AND_ACTIVE_FORM)
List<Group> findAllBuildingsByZipCodeAndActiveFrom(@Param("zipCode") String zipCode, @Param("date") LocalDate date);

This will give you a bulding that has atleast one contract that activeFrom > date

Also you can try with this query:

String FIND_ALL_BUILDINGS_BY_ZIP_CODE_AND_ACTIVE_FORM = "SELECT b FROM Building b" +
        " inner join b.contract as c " +
        " where c.activeFrom > :date " +
        " and b.zipCode = :zipCode";

Just make sure that you joined the second table before filtering

Upvotes: 0

Catia
Catia

Reputation: 11

I am going on the assumption you are looking for a JpaRepository query you could use. You can use "_" to apply queries to fields on embedded objects.

For example something like, findAllByBuilding_ZipCodeAndActiveFromBefore...

Upvotes: 1

Related Questions