CODEWITHSUNDEEP
hibernatepredicatespecificationshibernate-criteriacriteria-api
Grzegorz Kawalec
Grzegorz Kawalec

Reputation: 325

How to sort the query result by the value in one column, depending on the value in the second column using CriteriaBuilder

The query is built using Predicate and the Specification implementation and the result is the returned Page<>, and the example shown is a simplification of the entire database structure sufficient to represent the problem.

The data is returned based on two tables Car and Element.
The filtered value is based on the data in table Car, which is linked to table Elements with a one-to-many relationship.
The result should be sorted based on the values in table Element, but only for rows where the manufacturer column has the indicated value.

How to sort by Element_.value only if Element_.manufacturer = 5, and for the != 5 condition, e.g. comparing the value as null or leaving it unsorted.

@Getter
@Entity
@Immutable
@Table(name = "f_car")
public class Car {

    @Id
    private Long id;

    private String name;

    @OneToMany(fetch = FetchType.LAZY)
    @JoinColumn(name = "car_id", updatable = false, insertable = false)
    private List<Element> elements;
}

@Getter
@Entity
@Immutable
@Table(name = "f_element")
public class Element {

    @Id
    private Long id;

    @Column(name = "car_id")
    private Long carId;

    private Long manufacturer;

    private String value;
}


@AllArgsConstructor
public class SpecForView implements Specification<Car> {

    private final String name;
    private final Long manufacturerId;

    @Override
    public Predicate toPredicate(Root<Car> root, CriteriaQuery<?> cq, CriteriaBuilder cb) {
        Predicate namePredicate = cb.equal(root.get(Car_.name), name);

       // TODO: a place in the code to set the sort
}

Sample values in the database
enter image description here

Upvotes: 0

Views: 687

Answers (1)

Christian Beikov
Christian Beikov

Reputation: 16452

By using a case when statement:

Join<Car, Element> e = root.join(Car_.elements);
cq.orderBy(
  cb.selectCase()
    .when(
      cb.equal(e.get(Element_.manufacturer), manufacturerId), 
      e.get(Element_.value)
    ).otherwise(cb.nullLiteral(String.class))
);

Upvotes: 1

Related Questions