Spring hibernate orderBy on list element-CodePudding

@Entity
class Person{
   private int id;
   @OneToMany(mappedBy=owner)
   private List<Pet> pets;
}

@Entity
class Pet{
  private name;
  private ZonedDateTime birthDate;
  @ManyToOne
  @JoinColumn(name="owner_id")
  private Person owner;
}

I want to find all the persons and order them by their oldest pet birthday

The only way I can solve this is through @Formula , something like

@Entity
class Person{
   private int id;
   private List<Pet> pets;

   @Formula("(SELECT p.birth_date FROM pet p WHERE p.owner_id = id order by p.birth_date ASC LIMIT 1)")
   private ZonedDateTime oldestPetBirthday;
}

then

public List<Person> findPersonByOrderByOldestPetBirthdayAsc

But I don't want to touch raw sql, I am looking for something like

public List<Person> findPersonByOrderByPetsTop1OrderByBirthDateAsc

OR by using pageable something like:

PageRequest.of(page,pageSize,Sort.by(ASC, "pets.sort(BirthDateComparator).get(0)"))

is that possible?

CodePudding user response：

Try to use @OrderBy annotation from @javax.persistence.OrderBy package on your one to many collection object.

@OrderBy("birthDate")
private List<Pet> pets;

CodePudding user response：

Your solution with the formula is ok but suffers from some issues. Anyway, since you don't want to write SQL, you will have to use something like Blaze-Persistence Entity Views.

I created the library to allow easy mapping between JPA models and custom interface or abstract class defined models, something like Spring Data Projections on steroids. The idea is that you define your target structure(domain model) the way you like and map attributes(getters) via JPQL expressions to the entity model.

A DTO model for your use case could look like the following with Blaze-Persistence Entity-Views:

@EntityView(Person.class)
public interface PersonDto {
    @IdMapping
    Long getId();
    @Limit(limit = "1", order = "birthDate desc)
    @Mapping("pets")
    OldestPetDto getOldestPet();

    @EntityView(Pet.class)
    interface OldestPetDto {
        @IdMapping
        Long getId();
        ZonedDateTime getBirthDate();
    }
}

Querying is a matter of applying the entity view to a query, the simplest being just a query by id.

PersonDto a = entityViewManager.find(entityManager, PersonDto.class, id);

The Spring Data integration allows you to use it almost like Spring Data Projections: https://persistence.blazebit.com/documentation/entity-view/manual/en_US/index.html#spring-data-features

Page<PersonDto> findAll(Pageable pageable);

The best part is, it will only fetch the state that is actually necessary!

Also, you can add a Sort for oldestPet.birthDate and it will work just like you would like it to!