How to Select Only Specific Children in One to Many Relation in JPA

Question

Here below is a simple model for a pet shop...

Pet Class

@Entity
@Table(name = "pet")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public abstract class Pet {

  @Column(name = "id", nullable = false)
  private Long id;

  @Column(name = "name", nullable = false)
  private String name;

  @Column(name = "birth_date", nullable = false)
  private LocalDate birthDate;

  @Column(name = "death_date")
  private LocalDate deathDate;

  @ManyToOne
  @JoinColumn(name = "pet_shop_id", nullable = false, referencedColumnName = "id")
  @Setter(AccessLevel.NONE)
  private PetShop petShop;

  public void setPetShop(PetShop petShop) {
    setPetShop(petShop, true);
  }

  public void setPetShop(PetShop petShop, boolean add) {
    this.petShop= petShop;
    if (petShop!= null && add) {
      petShop.addPet(this, false);
    }
}

PetShop Class

@Entity
@Table(name = "pet_shop")
@Getter
@Setter
@NoArgsConstructor
@AllArgsConstructor
@EqualsAndHashCode
public class PetShop {

  @Column(name = "id", nullable = false)
  private Long id;

  ...

  @OneToMany(
      mappedBy = "petShop",
      fetch = FetchType.LAZY,
      cascade = {CascadeType.ALL})
  private List<Pet> pets= new ArrayList<>();

  public void addPet(final Pet pet) {
    addPet(pet, true);
  }

  public void addPet(final Pet pet, boolean set) {
    if (pet!= null) {
      if (pets.contains(pet)) {
        pets.set(pets.indexOf(pet), pet);
      } else {
        pets.add(pet);
      }
      if (set) {
        pet.setPetShop(this, false);
      }
    }
  }
}

PetShopRepository Interface

public interface PetShopRepository 
  extends JpaRepository<PetShop, Long> {

  @Query(
      "SELECT DISTINCT ps FROM PetShop ps"
          + " JOIN ps.pets p"
          + " WHERE ps.id = :id AND p.deathDate IS NULL")
  @Override
  Optional<PetShop> findById(@NonNull Long id);
}

... and here is how to create a PetShop with 2 Pet instances (one alive and another one dead):

final Pet alive = new Pet();
alive.setName("cat");
alive.setCall("meow");
alive.setBirthDate(LocalDate.now());

final Pet dead = new Pet();
dead.setName("cat");
dead.setCall("meow");
dead.setBirthDate(LocalDate.now().minusYears(15L));
dead.setDeathDate(LocalDate.now());

final PetShop petShop = new PetShop();
petShop.getPets().add(alive);
petShop.getPets().add(dead);

petShopRepositiry.save(petShop);

Now I want to retrieve the PetShop and I'd assume it contains only pets that are alive:

final PetShop petShop = petShopRepository.findById(shopId)
    .orElseThrow(() -> new ShopNotFoundException(shopId));

final int petCount = petShop.getPets().size(); // expected 1, but is 2

According to my custom query in PetShopRepository I'd expect petShop.getPets() returns a list with 1 element, but it actually returns a list with 2 elements (it includes also the dead pet).

Am I missing something? Any hint would be really appreciated :-)

Answer 1

This is because Jpa maintains the coherence of the relations despite your query.

I.e. : your query returns the shops having at least one pet alive. But, Jpa will return the shop with the complete set of pets. And you can probably see extra sql queries sent by Jpa (if you set show_sql=true) to refill pets collection on the returned shop.

Fundamently, it's not because you wanted to get the shops with living pets that these shops loose their dead pets.

To get it right you would have to design the pets collection so that it would filter the dead pets. Hibernate provides such annotations (@Filter and @FilterDef), but apparently JPA does not.

I don't think that filtering at @Postload would be a good idea, because you would have to put back the filtered dead pets in the collection before any flush in the database. That looks risky to me.