JPQL How to sum up a given property of children and fetch these children avoiding n + 1 selections?

user_15094401 0
Viewed 530

I have two entities: category and product. They are associated and category is a parent:

@Entity
@Table(name = "categories")
public class Category {
    @Id
    @GeneratedValue(generator = "inc")
    @GenericGenerator(name = "inc", strategy = "increment")
    private int id;
    private String name;
    private int totalQuantity;

    @OneToMany(fetch = FetchType.LAZY, mappedBy = "category")
    private Set<Product> products;
  
    public Category(int id, String name, int totalQuantity, Set<Product> products) {
        this.id = id;
        this.name = name;
        this.totalQuantity = totalQuantity;
        this.products = products;
    }

Product entity:

@Entity
@Table(name = "products")
public class Product {
    @Id
    @GeneratedValue(generator = "inc")
    @GenericGenerator(name = "inc", strategy = "increment")
    private int id;
    private String name;
    private int amount;
    @ManyToOne
    @JoinColumn(name = "category_id")
    private Category category
}

(totalQuantity is the sum of the amount of products associated to the category ) I want to get all categories and all associated products in such a way as to prevent n + 1 and do a summation. This is my query that is wrong/uncompleted because I have no idea how I can do/complete it:

@Query("SELECT new com.example.demo.category.Category(p.category.id, p.category.name, SUM(p.amount), ) FROM Product p GROUP BY p.category.id")
List<Category> findAll();

EDIT: To better show the goal, I add my category view ("Front", "Back-end") with products associated with each of them: Category view

spring-boot jpql jpa
2 Answers

I have a project with this feature, you can create a entity like this

SampleEntity

@OneToMany(cascade = CascadeType.ALL, fetch = FetchType.LAZY, mappedBy = "sample")
    private Set<AttributeEntity> attributes = new HashSet<>();

With a list of an entity. Create a DTO with a field with a long term like

SampleDTO

And when you cast the entity to DTO object you can use the count, in my case I`ve used the mapStruct plugin

SampleMapper


    public abstract List<SampleDTO> convert(List<SampleEntity> sampleEntities);

    public long map(Set<AttributeEntity> past) {
        return past.size();
    }

In my service I call find all

@Override
    public List<SampleDTO> findAll() {
        List<SampleEntity> entities = sampleRepository.findAll();
        return sampleMapper.convert(entities);
    }

and the repository is default for jpaRepository


public interface ISampleRepository extends JpaRepository<SampleEntity, UUID> , JpaSpecificationExecutor<SampleEntity> {

}
user_82231120

Use the @Formula keyword to do native sql to sum product amount.

// the p.category_id=id <-- this id is Category itself id
@Formula("(SELECT COALESCE(SUM(p.amount),0) FROM products p INNER JOIN categories c ON p.category_id=c.id WHERE p.category_id=id)")
private int totalQuantity;

Note: If your totalQuantity type is int, you need to use COALESCE avoid the null value. If it type is Integer, you don't need to use it.

Use LEFT JOIN FETCH to prevent N+1 problem.

@Query("SELECT DISTINCT c FROM Category c LEFT JOIN FETCH c.products")
List<Category> findAll()

The Hibernate sql result:

select distinct category0_.id                         as id1_6_0_,
                products1_.id                         as id1_11_1_,
                category0_.name                       as name2_6_0_,
                (SELECT COALESCE(SUM(p.amount), 0)
                 FROM product p
                          INNER JOIN categories c ON p.category_id = c.id
                 where p.category_id = category0_.id) as formula1_0_,
                products1_.amount                     as amount2_11_1_,
                products1_.category_id                as category4_11_1_,
                products1_.name                       as name3_11_1_,
                products1_.category_id                as category4_11_0__,
                products1_.id                         as id1_11_0__
from categories category0_
         left outer join products products1_ on category0_.id = products1_.category_id

You can use one query to get all categories and sum amount of products in the same time.

Old answer

You can use the LEFT JOIN FETCH to prevent n+1 problem.

@Query("SELECT DISTINCT c FROM Category c LEFT JOIN FETCH c.products")
List<Category> findAll();

Use the sum() operator calculate product amount.

List<Category> categories = categoryRepository.findAll().stream().peek(category -> {
    Set<Product> products = category.getProducts();
    if (!ObjectUtils.isEmpty(products)) {
        int totalQuantity = products.stream().mapToInt(Product::getAmount).sum();
        category.setTotalQuantity(totalQuantity);
    }
}).collect(Collectors.toList());

Finally, you can use one query to get all categories and count the total amount of products.

user_50468870
Related