How to use a group by Sum SQL with Spring Data JPA

问题:

I want to load best seller products by quantity. These are my tables:

Product
id  name
1    AA   
2    BB

Productorder
order_id  product_id  quantity
1          1          10      
2          1          100    
3          2          15     
4          1          15       

This is my Spring Data Repository:

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = "select top 5 p.name, sum(po.quantity) as total_quantity from product p " +
            "inner join productorder po " +
                "on p.id = po.product_id " +
            "group by p.id, p.name " +
            "order by total_quantity desc", nativeQuery = true)
    List<Product> findTopFiveBestSeller();
}

I am getting HsqlException: Column not found: id

I think this error does not have anything to do with id column, as it is there for both tables. Do "group by Sum queries" work with Spring data? Because it seems little strange for me as Spring Data should select just product attributes from the database, and with this sql we are selecting also the sum(po.quantity). Can Spring data handle this and convert the result as a List?

PS: I am using HSQLDB embedded as DB.

回答1:

After changing the select statements projection from p.name to p.* to indicate that I am selecting multiple values rather than just String objects that have to be magically converted to Product objects, this works:

@Repository
public interface ProductRepository extends JpaRepository<Product, Long> {

    @Query(value = "select top 5 p.*, sum(po.quantity) as total_quantity from product p " +
        "inner join productorder po " +
            "on p.id = po.product_id " +
        "group by p.id, p.name " +
        "order by total_quantity desc", nativeQuery = true)
    List<Product> findTopFiveBestSeller();

}

Thanks @JMK und @JB Nizet.