[FIXED] How to sort by ManyToOne list in Pageable without duplicates?

Issue

I recently ran into this problem. I have a product that has a list of values in relation to volume. Example: enter image description here Entities :

public class Price implements Serializable, Comparable<Price> {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "product_id")
    private Product product;

    private int valume;
    private int cost;

    @Override
    public int compareTo(Price o) {
        if (this.valume > o.getValume()) {
            return 1;
        } else if (this.valume < o.getValume()) {
            return -1;
        } else {
            return 0;
        }
    }

}

public class Product implements Serializable {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private long id;

    private String name;
    private String description;
    private LocalDateTime data;

    @OneToMany(targetEntity = Price.class, mappedBy = "product",
            cascade = CascadeType.ALL)
    @LazyCollection(LazyCollectionOption.FALSE)
    private List<Price> price = new ArrayList<>();    

}

Controller :

@GetMapping
    public String tapePage(@PageableDefault(size = 12, sort = "data", direction = Sort.Direction.DESC) Pageable pageable,
            Model model){
        model.addAttribute("products", productService.getAllProducts(pageable));
        return "tape";
    }

The problem is that if I want to sort a product by its cost, I will be given duplicate objects that have several values. Example - http://localhost:8080/?sort=price.valume,ASC How can I implement a request that will issue products for non-duplicates with different prices. For example: http://localhost:8080/?sort=price[0].valume,ASC

Solution

That's not directly possible but you can achieve that with 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(Product.class)
public interface ProductDto {
    @IdMapping
    Long getId();
    String getName();
    String getDescription();
    LocalDateTime getData();
    @Mapping("MAX(prices.valume) OVER (PARTITION BY id)")
    int getHighestValuem();
    Set<PriceDto> getPrice();

    @EntityView(Price.class)
    interface PriceDto {
        @IdMapping
        Long getId();
        int getValume();
        int getCost();
    }
}

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

ProductDto a = entityViewManager.find(entityManager, ProductDto.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<ProductDto> findAll(Pageable pageable);

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

In your case, you could use a sort like this: sort=highestValuem,ASC

Answered By - Christian Beikov