I wrote a function to filter product. Some attributes are in product (categoryId, minPrice, maxPrice, soldCount, priceSort), others are in productDetail (sizeId, colorId, materialId). Here’s my function:
@PersistenceContext
private EntityManager entityManager;
@Override
public Page<Product> getFilteredProducts(ProductFilterDto filter, Pageable pageable) {
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
Root<Product> countRoot = countQuery.from(Product.class);
countQuery.select(cb.countDistinct(countRoot));
Join<Product, ProductDetail> countJoin = countRoot.join("productDetails", JoinType.INNER);
List<Predicate> countPredicates = new ArrayList<>();
if (filter.getCategoryIds() != null && !filter.getCategoryIds().isEmpty()) {
countPredicates.add(countRoot.get("categoryId").in(filter.getCategoryIds()));
}
if (filter.getSizeIds() != null && !filter.getSizeIds().isEmpty()) {
countPredicates.add(countJoin.get("sizeId").in(filter.getSizeIds()));
}
if (filter.getColorIds() != null && !filter.getColorIds().isEmpty()) {
countPredicates.add(countJoin.get("colorId").in(filter.getColorIds()));
}
if (filter.getMaterialIds() != null && !filter.getMaterialIds().isEmpty()) {
countPredicates.add(countJoin.get("materialId").in(filter.getMaterialIds()));
}
if (filter.getFromPrice() != null) {
countPredicates.add(cb.greaterThanOrEqualTo(countRoot.get("minPrice"), filter.getFromPrice()));
}
if (filter.getToPrice() != null) {
countPredicates.add(cb.lessThanOrEqualTo(countRoot.get("maxPrice"), filter.getToPrice()));
}
if (filter.getSearchValue() != null && !filter.getSearchValue().isEmpty()) {
countPredicates.add(cb.like(cb.lower(countRoot.get("name")), "%" + filter.getSearchValue().toLowerCase() + "%"));
}
countQuery.where(countPredicates.toArray(new Predicate[0]));
// Thực hiện truy vấn count
Long totalCount = entityManager.createQuery(countQuery).getSingleResult();
// Query để lấy dữ liệu phân trang
CriteriaQuery<Product> query = cb.createQuery(Product.class);
Root<Product> root = query.from(Product.class);
Join<Product, ProductDetail> productDetailJoin = root.join("productDetails", JoinType.INNER);
List<Predicate> predicates = new ArrayList<>();
// Điều kiện lọc cho truy vấn dữ liệu phân trang
if (filter.getCategoryIds() != null && !filter.getCategoryIds().isEmpty()) {
predicates.add(root.get("categoryId").in(filter.getCategoryIds()));
}
if (filter.getSizeIds() != null && !filter.getSizeIds().isEmpty()) {
predicates.add(productDetailJoin.get("sizeId").in(filter.getSizeIds()));
}
if (filter.getColorIds() != null && !filter.getColorIds().isEmpty()) {
predicates.add(productDetailJoin.get("colorId").in(filter.getColorIds()));
}
if (filter.getMaterialIds() != null && !filter.getMaterialIds().isEmpty()) {
predicates.add(productDetailJoin.get("materialId").in(filter.getMaterialIds()));
}
if (filter.getFromPrice() != null) {
predicates.add(cb.greaterThanOrEqualTo(root.get("minPrice"), filter.getFromPrice()));
}
if (filter.getToPrice() != null) {
predicates.add(cb.lessThanOrEqualTo(root.get("maxPrice"), filter.getToPrice()));
}
if (filter.getSearchValue() != null && !filter.getSearchValue().isEmpty()) {
predicates.add(cb.like(cb.lower(root.get("name")), "%" + filter.getSearchValue().toLowerCase() + "%"));
}
query.where(predicates.toArray(new Predicate[0]));
// Thêm điều kiện sắp xếp
List<Order> orders = new ArrayList<>();
if (filter.getNewest() != null && filter.getNewest()) {
orders.add(cb.desc(root.get("createdAt")));
} else if (filter.getBestSeller() != null && filter.getBestSeller()) {
orders.add(cb.desc(root.get("soldCount")));
}
if (filter.getPriceSort() != null) {
if ("asc".equalsIgnoreCase(filter.getPriceSort())) {
orders.add(cb.asc(root.get("minPrice")));
} else if ("desc".equalsIgnoreCase(filter.getPriceSort())) {
orders.add(cb.desc(root.get("minPrice")));
}
}
query.orderBy(orders);
// Thực hiện truy vấn phân trang
TypedQuery<Product> typedQuery = entityManager.createQuery(query);
typedQuery.setFirstResult(pageable.getPageNumber() * pageable.getPageSize());
typedQuery.setMaxResults(pageable.getPageSize());
List<Product> products = typedQuery.getResultList();
return new PageImpl<>(products, pageable, totalCount);
}
And Here’s my service:
public PaginationWrapper<ProductResponse> filterProducts(ProductFilterDto request, int page, int pageSize, String sortBy, String direction) {
Sort sort = Sort.by(Sort.Direction.fromString(direction), sortBy);
Pageable pageable = PageRequest.of(page - 1, pageSize, sort);
Page<Product> productPages = productRepository.getFilteredProducts(request, pageable);
List<ProductResponse> productResponses = productPages.getContent().stream()
.map(product -> {
ProductResponse response = productMapper.toProductResponse(product);
response.setProductImages(getProductImagesLink(product.getProductId()));
response.setPercent(getProductSale(product.getProductId()));
return response;
})
.collect(Collectors.toList());
PaginationInfo paginationInfo = PaginationInfo.builder()
.totalCount(productPages.getTotalElements())
.totalPages((int) Math.ceil((double) productPages.getTotalElements() / pageSize))
.hasNext(productPages.hasNext())
.hasPrevious(productPages.hasPrevious())
.build();
return new PaginationWrapper<>(productResponses, paginationInfo);
}
When I test on Swagger, each time I change the pageSize value, totalCount changes also. I don’t know why and I hope you guys can help me =((