Spring Data JPA order by value from OneToMany relation

user_2092052 0
Viewed 1084

I am trying to sort a result by nested collection element value. I have a very simple model:

@Entity
public class User {

    @Id
    @NotNull
    @Column(name = "userid")
    private Long id;

    @OneToMany(mappedBy = "user")
    private Collection<Setting> settings = new HashSet<>();

    // getters and setters
}

@Entity
public class Setting {

    @Id
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "userid")
    private User user;

    private String key;
    private String value;

    // getters and setters
}

public interface UserRepository extends JpaRepository<User, Long>, QuerydslPredicateExecutor<User> {
}

I want to have a result returned sorted by the value of one setting. Is it possible to order by user.settings.value where settings.name = 'SampleName' using Spring Data JPA with QueryDSL?

querydsl spring-data spring-data-jpa jpa
1 Answers

I've used JpaSpecificationExecutor. let's see findAll for example.

Page<T> findAll(@Nullable Specification<T> spec, Pageable pageable);

Before call this method you can create your specification dynamically (where condition) and Pageable object with dynamic Sort information.

For example

...

Specification<T> whereSpecifications = Specification.where(yourWhereSpeficiation);
Sort sortByProperty = Sort.by(Sort.Order.asc("property"));
PageRequest orderedPageRequest = PageRequest.of(1, 100, sortByProperty);

userRepository.findAll(whereSpecifications, PageRequest.of(page, limit, orderedPageRequest));
user_30412290
Related