Hibernate Criterion on joined table showing unexpected results

Question

I have a criteria that looks like this:

public List<role> searchByFormStatus(boolean status) {
    Criteria criteria = this.getSession().createCriteria(this.getPersistentClass());
    List<role> result = (List<role>) criteria                               
                        .setFetchMode("role", FetchMode.JOIN)
                        .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
                        .createAlias("formApprovals", "f")
                        .add(Restrictions. eq("f.latestApproval", true))
                        .list();
    return result;                          
}

At first look it should be working, but no matter if I send true or false value in the parameter, the result will always be

[{
    "roleIsActive": true,
    "roleName": "role1",
    "roleNotes": "note",
    "formApprovals": [
        {
            "approvalNotes": "good",
            "approvedDate": 1449900000000,
            "fkapprovedBy": 1,
            "idformApproval": 1,
            "latestApproval": true
        },
        {
            "approvalNotes": "bad",
            "approvedDate": 1449900000000,
            "fkapprovedBy": 1,
            "idformApproval": 2,
            "latestApproval": false
        }
}]

As you can see, the "formApprovals" brings all registers in the database, even if I create the restriction for the latestApproval property

The property declaration in the parent object (role) is:

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="role")
public Set<FormApproval> getFormApprovals() {
    return this.formApprovals;
}

public void setFormApprovals(Set<FormApproval> formApprovals) {
    this.formApprovals = formApprovals;
}

Checking the console, I can see that the where clause is being generated properly by hibernate, however, I can see that after that there are other queries, is it possible that those queries (I have no idea why are they there) are overwriting my criteria?

Any ideas?

EDIT

FormApproval Class

@Entity
@Table(name="FormApproval"
    ,catalog="catalog"
)
public class FormApproval  implements java.io.Serializable {

    private static final long serialVersionUID = 8L;
     private int idformApproval;
     private role role;
     private Integer fkapprovedBy;
     private Date approvedDate;
     private String approvalNotes;
     private boolean latestApproval;

    public FormApproval() {
    }


    public FormApproval(int idformApproval, role role) {
        this.idformApproval = idformApproval;
        this.role = role;
    }
    public FormApproval(int idformApproval, role role, Integer fkapprovedBy, Date approvedDate, String approvalNotes, boolean latestApproval) {
       this.idformApproval = idformApproval;
       this.role = role;
       this.fkapprovedBy = fkapprovedBy;
       this.approvedDate = approvedDate;
       this.approvalNotes = approvalNotes;
       this.latestApproval = latestApproval; 
    }

     @Id @GeneratedValue(strategy = IDENTITY) 

    @Column(name="IDFormApproval", unique=true, nullable=false)
    public int getIdformApproval() {
        return this.idformApproval;
    }

    public void setIdformApproval(int idformApproval) {
        this.idformApproval = idformApproval;
    }

@JsonIgnore
@ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="FKRole", nullable=false)
    public role getrole() {
        return this.role;
    }

    public void setrole(role role) {
        this.role = role;
    }


    @Column(name="LatestApproval")
    public boolean getLatestApproval() {
        return this.latestApproval;
    }

    public void setLatestApproval(boolean latestApproval) {
        this.latestApproval = latestApproval;
    }

    @Column(name="FKApprovedBy")
    public Integer getFkapprovedBy() {
        return this.fkapprovedBy;
    }

    public void setFkapprovedBy(Integer fkapprovedBy) {
        this.fkapprovedBy = fkapprovedBy;
    }
    @Temporal(TemporalType.DATE)
    @Column(name="ApprovedDate", length=10)
    public Date getApprovedDate() {
        return this.approvedDate;
    }

    public void setApprovedDate(Date approvedDate) {
        this.approvedDate = approvedDate;
    }

    @Column(name="ApprovalNotes")
    public String getApprovalNotes() {
        return this.approvalNotes;
    }

    public void setApprovalNotes(String approvalNotes) {
        this.approvalNotes = approvalNotes;
    }
}

Role Class

@Entity
@Table(name="Role"
    ,catalog="catalog"
)    
public class Role  implements java.io.Serializable {

    private static final long serialVersionUID = 1L;
    private int idRole;
     private WorkType workType;
     private String roleName;
     private String roleNotes;
     private boolean roleIsActive;
     private Set<FormApproval> formApprovals = new HashSet<FormApproval>(0);
     private Set<Topicrole> topicRoles = new HashSet<TopicRole>(0);
     private Set<FormFeedBack> formFeedBacks = new HashSet<FormFeedBack>(0);
     private Set<UserRole> userRoles = new HashSet<UserRrole>(0);
     private Set<Interview> interviews = new HashSet<Interview>(0);

    public Role() {
    }


    public Role(int idRole, WorkType workType, String roleName, boolean roleIsActive) {
        this.idRole = idRole;
        this.workType = workType;
        this.RoleName = RoleName;
        this.roleIsActive = roleIsActive;
    }
    public Role(int idRole, WorkType workType, String roleName, String roleNotes, boolean roleIsActive, Set<FormApproval> formApprovals, Set<TopicRole> topicRoles, Set<FormFeedBack> formFeedBacks, Set<UserRole> userRoles, Set<Interview> interviews) {
       this.idRole = idRole;
       this.workType = workType;
       this.RoleName = RoleName;
       this.roleNotes = roleNotes;
       this.roleIsActive = roleIsActive;
       this.formApprovals = formApprovals;
       this.topicRoles = topicRoles;
       this.formFeedBacks = formFeedBacks;
       this.userRoles = userRoles;
       this.interviews = interviews;
    }

     @Id @GeneratedValue(strategy = IDENTITY) 

    @Column(name="IDRole", unique=true, nullable=false)
    public int getIdrole() {
        return this.idRole;
    }

    public void setIdrole(int idRole) {
        this.idRole = idRole;
    }
@ManyToOne(fetch=FetchType.EAGER)
    @JoinColumn(name="FKWorkType", nullable=false)
    public WorkType getWorkType() {
        return this.workType;
    }

    public void setWorkType(WorkType workType) {
        this.workType = workType;
    }

    @Column(name="RoleName", nullable=false)
    public String getRoleName() {
        return this.roleName;
    }

    public void setRoleName(String roleName) {
        this.roleName = roleName;
    }

    @Column(name="RoleNotes")
    public String getRoleNotes() {
        return this.roleNotes;
    }

    public void setRoleNotes(String roleNotes) {
        this.roleNotes = roleNotes;
    }

    @Column(name="RoleIsActive", nullable=false)
    public boolean isRoleIsActive() {
        return this.roleIsActive;
    }

    public void setRoleIsActive(boolean roleIsActive) {
        this.roleIsActive = roleIsActive;
    }

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="role")
    public Set<FormApproval> getFormApprovals() {
        return this.formApprovals;
    }

    public void setFormApprovals(Set<FormApproval> formApprovals) {
        this.formApprovals = formApprovals;
    }

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="role")
    public Set<TopicRole> getTopicRoles() {
        return this.topicRoles;
    }

    public void setTopicRoles(Set<TopicRole> topicRoles) {
        this.topicRoles = topicRoles;
    }

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="role")
@JsonManagedReference
    public Set<FormFeedBack> getFormFeedBacks() {
        return this.formFeedBacks;
    }

    public void setFormFeedBacks(Set<FormFeedBack> formFeedBacks) {
        this.formFeedBacks = formFeedBacks;
    }

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="role")
    public Set<UserRole> getUserRoles() {
        return this.userRoles;
    }

    public void setUserRoles(Set<UserRole> userRoles) {
        this.userRoles = userRoles;
    }

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="acnrole")
    public Set<Interview> getInterviews() {
        return this.interviews;
    }

    public void setInterviews(Set<Interview> interviews) {
        this.interviews = interviews;
    }

}

Answer 1

Fixed using

.createCriteria("formApprovals","f",1,Restrictions.eq("f.latestApproval", status))

1 forces a Left outer join in the query

Answer 2

You might try using the restriction as part of ON clause on top of the association.

@OneToMany(cascade=CascadeType.ALL, fetch=FetchType.EAGER, mappedBy="role")
@Where(clause=" latestApproval='true' ")
public Set<FormApproval> getFormApprovals() {
    return this.formApprovals;
}

Also you can eliminate setting FetchMode.JOIN fetch by pointing to the association path using the method org.hibernate.Criteria.createAlias(String associationPath, String alias, JoinType joinType)

public List<role> searchByFormStatus(boolean status) {
    Criteria criteria = this.getSession().createCriteria(Role.class, "role")
            .setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY)
            .createAlias("role.formApprovals", "formApprovals", JoinType.LEFT_OUTER_JOIN);
    return criteria.list();
}

Update:

For older versions of hibernate, use the other method below. Where joinType value would be 1 for LEFT_OUTER_JOIN and join clause can be passed as the 4th argument.

public Criteria createAlias(String associationPath, String alias, int joinType, Criterion withClause) throws HibernateException;

Answer 3

With EAGER fetch types, a nested criteria should work for this case:

public List<role> searchByFormStatus(boolean status) {
    Criteria criteria = this.getSession().createCriteria(this.getPersistentClass());
    List<role> result = (List<role>) criteria                               
                        .createCriteria("formApprovals")
                             .add(Restrictions.eq("latestApproval", true))
                        .list();
    return result;                          
}