Is there any way to use one value from retrieved List in HQL?

Question

I am trying to map an Entity to respective DTO list using HQL. Is there any way to select only the first element of an URL list and the latest date element from a Map?

Here is the query I am trying to build:

@Query("SELECT NEW ProductShownInListDto(p.name, p.price.getLatest(), p.productImages.get(1)) FROM Product p")
public List<ProductShownInListDto> getProductsToShowInList();

Here is the DTO:

public class ProductShownInListDto implements Serializable {

    private String name;
    private BigDecimal price;
    private URL productImage;

}

Here is the product entity:

public class Product implements Serializable {

    // [...] Other fields

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

    @NotNull
    @ElementCollection
    private Map<LocalDate, BigDecimal> prices;

    @NotNull
    @ElementCollection
    private List<URL> productImages;

}

score 1 · Answer 1 · answered Jan 16 '23 at 18:14

This can't be done using projections because subqueries are only allowed in where and having clauses, and entity methods aren't allowed at all, because there is no way to transform them into native sql.

You could achieve this by creating a database view that have the fields you need, mapping that view to an immutable entity, and write jpql query fetching data from view's entity instead of product entity.

Is there any way to use one value from retrieved List in HQL?

1 Answers1