Dynamic instantiation in a sub-query is unsupported

Question

I have a Car entity that contains a list of Price entities. When retrieving the car from the database I can map it directly to my ViewCar DTO, but not so the list of prices.

Here is my ViewCar Dto:

public class ViewCar {

    private Long id;

    private String name;

    private Long uniqueId;

    private CarStatus status;

    private Double defaultPrice;

    private String location;

    private String city;

    List<ViewPrice> priceList;

    public ViewCar(Long id, String name, Long uniqueId, CarStatus status, Double defaultPrice, String location, String city, List<ViewPrice> priceList) {
        this.id = id;
        this.name = name;
        this.uniqueId = uniqueId;
        this.status = status;
        this.defaultPrice = defaultPrice;
        this.location = location;
        this.city = city;
        this.priceList = priceList;
    }
}

This is one implementation that I tried using EntityManager:

TypedQuery<ViewCar> query = entityManager.createQuery(
                "SELECT NEW com.example.core.car.dto.response.ViewCar(c.id, c.name, c.uniqueId, c.status, c.defaultPrice, c.location.name, c.city.name, " +
                        "(SELECT NEW com.example.core.price.dto.response.ViewPrice(p.value, p.from_date, p.to_date) FROM Price p WHERE p.car = c)) " +
                        "FROM Car c WHERE c.id = 21",
                ViewCar.class);

And also in the JpaRepository<Car, Long>

@Query("SELECT new com.example.core.car.dto.response.ViewCar"
            + "(c.id, c.name, c.uniqueId, c.status, c.defaultPrice, c.location.name, c.city.name, " +
            "(SELECT new com.example.core.price.dto.response.ViewPrice(p.value, p.from_date, p.to_date) " +
            "FROM Price p where p.car.id = ?1 )) FROM Car c WHERE c.id = ?1 AND c.deletedAt IS NULL")
    ViewCar findCarById2(@Param("id") Long id);

The error says: Dynamic instantiation in a sub-query is unsupported

I'm using Postgresql.

Answer 1

Not sure what kind of answer you are hoping to get, the error message is pretty clear. JPA is not a bean mapping framework, and the NEW operator is very limited in JPQL.

If your goal is to return a view of the entity with certain data omitted, have a look at projections. Note that, since you want nested projections, you'll need to use interfaces rather than DTOs.

Otherwise just return raw entities from the query and map them to DTOs in your code (there are plenty of mapping libraries available like MapStruct, Dozer, Orika etc. that make the task easier). There's no real benefit to trying to force JPQL to do the mapping.