Does not work left join with on predicate Criteria API

88 views Asked by At
@Table(name = "ERRORS_TYPE")
public class ErrorType extends Type {

    @OneToMany(mappedBy = "errorType", cascade = CascadeType.ALL, orphanRemoval = true)
    @ToString.Exclude
    private List<ErrorLegalAct> errorLegalAct;

    @OneToMany(mappedBy = "errorType", cascade = CascadeType.ALL, orphanRemoval = true)
    @ToString.Exclude
    private List<ErrorAction> errorAction;

    @OneToOne(mappedBy = "errorType", cascade = CascadeType.ALL, orphanRemoval = true)
    @ToString.Exclude
    private ErrorComment comment;

    @Column(name = "LAST_MODIFICATION")
    private LocalDateTime lastModification;
}

@Data
@MappedSuperclass
public abstract class Type {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private BigDecimal id;
    private Integer code;
    private String name;
    private LocalDateTime added;
    private LocalDateTime lastModification;
}

@Data
@Entity
@Table(name = "ERROR_LEGAL_ACTS")
public class ErrorLegalAct {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private BigDecimal id;
    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "ERROR_ID")
    private ErrorType errorType;
    @Column(name = "ACT")
    private String act;
    @Column(name = "DESCRIPTION")
    private String description;
    @Column(name = "ACTIVE")
    private Boolean active;
    @Column(name = "ADDED")
    private LocalDateTime added;
    @Column(name = "LAST_MODIFICATION")
    private LocalDateTime lastModification;
}

@Data
@Entity
@Table(name = "ERROR_ACTIONS")
public class ErrorAction {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private BigDecimal id;
    @ManyToOne
    @JoinColumn(name = "ERROR_ID", nullable = false)
    private ErrorType errorType;
    @Column(name = "ORDER_NUMBER")
    private Integer orderNumber;
    @Column(name = "ACTION")
    private String action;
    @Column(name = "SAMPLE")
    private String sample;
    @Column(name = "ACTIVE")
    private Boolean active;
    @Column(name = "ADDED")
    private LocalDateTime added;
    @Column(name = "LAST_MODIFICATION")
    private LocalDateTime lastModification;
}

@Data
@Entity
@Table(name = "ERROR_COMMENTS")
public class ErrorComment {
    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private BigDecimal id;
    @OneToOne
    @JoinColumn(name = "ERROR_ID")
    private ErrorType errorType;
    @Column(name = "ERROR_COMMENT")
    private String comment;
    @Column(name = "ADDED")
    private LocalDateTime added;
    @Column(name = "LAST_MODIFICATION")
    private LocalDateTime lastModification;
}


public Page<ErrorType> getErrorTypeByParams(Boolean active, String code, String name, Pageable pageable) {
        CriteriaBuilder cb = em.getCriteriaBuilder();
        CriteriaQuery<ErrorType> query = cb.createQuery(ErrorType.class);
        Root<ErrorType> root = query.from(ErrorType.class);
        Join<ErrorType, ErrorLegalAct> actJoin = root.join("errorLegalAct", JoinType.LEFT);
        Join<ErrorType, ErrorAction> actionJoin = root.join("errorAction", JoinType.LEFT);



        List<Predicate> predicates = new ArrayList<>();

        if (code != null) {
            predicates.add(cb.equal(root.get("code"), code));
        }

        if (name != null) {
            predicates.add(cb.equal(root.get("name "), name));
        }


        query.select(root).distinct(true);

        if (active != null) {
            actJoin.on(cb.equal(actJoin.get("active"), active));
            actionJoin.on(cb.equal(actionJoin.get("active"), active));
            predicates.add(cb.equal(root.get("active"), active));
        }

        if (!predicates.isEmpty()) {
            query.where(cb.and(predicates.toArray(new Predicate[0])));
        }

        List<ErrorType> result = em.createQuery(query)
                .setFirstResult((int)pageable.getOffset())
                .setMaxResults(pageable.getPageSize())
                .getResultList();

        return new PageImpl<>(result, pageable, result.size());
}

'm trying to get all the records with activity true, and all related elements whose activity is also true. Instead, I always get entries in related elements where the activity is false.

Example:

{
"error_type": {
"id": 1,
"active: "true"
"actions": [
      {
        "id": 1,
        "active": "true"
      },
      {
        "id": 2,
        "active": "false"
      }
    ],
    "legal_acts": [
      {
        "id": 1,
        "active": "true"
      }
    ]
  }
}

I tried both in "where", and "inner join", but still the result is not correct.

Join<ErrorType, ErrorLegalAct> actJoin = root.join("errorLegalAct", JoinType.INNER);
Join<ErrorType, ErrorAction> actionJoin = root.join("errorAction", JoinType.INNER);

if (active != null) {
    predicates.add(cb.equal(root.get("active"), active));
    predicates.add(cb.equal(actJoin.get("active"), active));
    predicates.add(cb.equal(actionJoin.get("active"), active));
}

I also tried to do this with QueryDSL, same result. What am I missing?

0

There are 0 answers