Criteria Builder With Sort And Distinct

Question

I am getting this error:

Caused by: org.postgresql.util.PSQLException: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list Position: 1272 at [email protected]//org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2476)

when I send a curl request that reaches this level of code (i.e. when I sort by unitPrice) :

if(sort.equals("price")) {
        Expression<Object> sortOrderExpression = criteriaBuilder.selectCase()
          .when(criteriaBuilder.isNotNull(root.get("unitDiscountPrice")), root.get("unitDiscountPrice"))
          .otherwise(root.get("unitPrice"));

it fails, but when I sort by id or any other field it works! Can you please help me detect or how to debug the exact cause please; Thanks

  public List<ProductEntity> fetchProducts(Integer page, Integer perPage, String search, String sort, Boolean descending, Long localeId, String brands, String categories, String colors, Long minPrice, Long maxPrice) {
    int limit = perPage != null ? perPage : LIMIT;
    if(page == null) {
      page = 1;
    }
    System.out.println("LOCALE ID:");
    System.out.println(localeId);
    if (localeId == null) {
      localeId = 1L;
    }
    int offset = limit * (page - 1);


    CriteriaBuilder criteriaBuilder = em.getCriteriaBuilder();
    CriteriaQuery<Object[]> criteriaQuery = criteriaBuilder.createQuery(Object[].class);
    Root<ProductEntity> root = criteriaQuery.from(ProductEntity.class);
    Join<ProductEntity, ProductTranslationEntity> translationJoin = root.join("productTranslationEntities", JoinType.LEFT);
    Join<ProductEntity, ProductPriceEntity> productPriceJoin = root.join("productPrice", JoinType.LEFT);

    translationJoin.on(criteriaBuilder.or(
      criteriaBuilder.equal(translationJoin.get("locale"), localeId),
      criteriaBuilder.isNull(translationJoin.get("locale"))
    ));
    List<Predicate> predicates = new ArrayList<>();
    if (brands != null && !brands.isEmpty()) {
      System.out.println("I AM IN Brand!");
      Join<ProductEntity, BrandEntity> brandJoin = root.join("brand", JoinType.INNER);
      List<String> brandList = Arrays.asList(brands.split(","));
      predicates.add(brandJoin.get("id").in(brandList));
    }
    // Handle CSV values for categories
    if (categories != null && !categories.isEmpty()) {
      System.out.println("I AM IN CATEGORY!");
      Join<ProductEntity, CategoryEntity> categoryJoin = root.join("categories", JoinType.INNER);

      List<String> categoryList = Arrays.asList(categories.split(","));
      predicates.add(categoryJoin.get("id").in(categoryList));
    }
    // Handle CSV values for categories
    if (colors != null && !colors.isEmpty()) {
      Join<ProductPriceEntity, VariationEntity> productPriceVariationJoin = productPriceJoin.join("variations", JoinType.LEFT);
      List<String> colorList = Arrays.asList(colors.split(","));
      predicates.add(productPriceVariationJoin.get("id").in(colorList));
    }

    // Handle "like" query for product name
    if (search != null && !search.isEmpty()) {
      predicates.add(criteriaBuilder.or(
          criteriaBuilder.like(criteriaBuilder.lower(root.get("name")), "%" + search.toLowerCase() + "%"),
          criteriaBuilder.like(criteriaBuilder.lower(root.get("description")), "%" + search.toLowerCase() + "%"),
          criteriaBuilder.like(criteriaBuilder.lower(translationJoin.get("productName")), "%" + search.toLowerCase() + "%"),
          criteriaBuilder.like(criteriaBuilder.lower(translationJoin.get("productDescription")), "%" + search.toLowerCase() + "%")
        )
      );
    }
    if((minPrice != null || maxPrice != null)) {
      if(minPrice == null) {
        minPrice = 0L;
      }
      if(maxPrice == null) {
        maxPrice = Long.MAX_VALUE;
      }
      predicates.add(criteriaBuilder.or(
          criteriaBuilder.between(root.get("unitPrice"), minPrice, maxPrice),
          criteriaBuilder.between(root.get("unitDiscountPrice"), minPrice, maxPrice),
          criteriaBuilder.between(productPriceJoin.get("price"), minPrice, maxPrice),
          criteriaBuilder.between(productPriceJoin.get("discountedPrice"), minPrice, maxPrice)
        )
      );
    }
    criteriaQuery.select(criteriaBuilder.array(
      criteriaBuilder.coalesce(translationJoin.get("productName"), root.get("name")),
      criteriaBuilder.coalesce(translationJoin.get("productDescription"), root.get("description")),
      root
    ));

    Order order;
    order = criteriaBuilder.desc(root.get("id"));
    // Handle sorting
    if (sort != null && !sort.isEmpty()) {
      String sortBy = descending != null && descending ? "desc" : "asc";

      if(sort.equals("price")) {
        Expression<Object> sortOrderExpression = criteriaBuilder.selectCase()
          .when(criteriaBuilder.isNotNull(root.get("unitDiscountPrice")), root.get("unitDiscountPrice"))
          .otherwise(root.get("unitPrice"));
        if ("asc".equalsIgnoreCase(sortBy)) {
          order = criteriaBuilder.asc(sortOrderExpression);
        } else if ("desc".equalsIgnoreCase(sortBy)) {
          order = criteriaBuilder.desc(sortOrderExpression);
        } else {
          // Handle invalid sortBy parameter, or provide a default sorting strategy
          order = criteriaBuilder.desc(root.get("id"));
        }
      } else {
        if ("asc".equalsIgnoreCase(sortBy)) {
          order = criteriaBuilder.asc(root.get(JSON_TO_DB_FIELDS.get(sort)));
        } else if ("desc".equalsIgnoreCase(sortBy)) {
          order = criteriaBuilder.desc(root.get(JSON_TO_DB_FIELDS.get(sort)));
        } else {
          // Handle invalid sortBy parameter, or provide a default sorting strategy
          order = criteriaBuilder.desc(root.get("id"));
        }
      }
    }
    // Apply the predicates to the criteria query
    criteriaQuery.where(predicates.toArray(new Predicate[0])).orderBy(order).distinct(true);
    return this.transformObjectToProductEntityList(em.createQuery(criteriaQuery).setMaxResults(limit).setFirstResult(offset).getResultList());
  }

Answer 1

So, the issue is that I wasn't adding the order by selectCase() itself to the query. A quick example would be:

You can't:

SELECT * FROM table1 ORDER BY CASE WHEN bla = 'bla1' ELSE blaaa END;

it should be

SELECT *, CASE WHEN bla = 'bla1' ELSE blaaa END FROM table1 ORDER BY CASE WHEN bla = 'bla1' ELSE blaaa END; 

So, I just needed to add:

selectCase()
          .when(criteriaBuilder.isNotNull(root.get("unitDiscountPrice")), root.get("unitDiscountPrice"))
          .otherwise(root.get("unitPrice"))

to my:

criteriaQuery.select(criteriaBuilder.array(
      criteriaBuilder.coalesce(translationJoin.get("productName"), root.get("name")),
      criteriaBuilder.coalesce(translationJoin.get("productDescription"), root.get("description")),
      root
    ));

Thanks for the help !