I've 4 entities: Parent, Alpha, Omega, Beta.
@Audited @Entity @Table(name = "parent")
public class Parent extends AuditEntity {
@Id @Column(nullable = false)
@GeneratedValue ....
private Long id;
@OneToMany(mappedBy = "parent", fetch = FetchType.LAZY, cascade = CascadeType.ALL,
orphanRemoval = true)
@JsonIgnore
private Set<Alpha> alphas;
}
/**
* Alpha connects Parent & Omega by containing their Ids mapping. It uses ParentAlphaId.java as ID class.
*/
@Audited @Entity @Table (name = "alpha")
@IdClass(ParentAlphaId.class)
public class Alpha extends AuditEntity {
@Id
@Column(name = "parent_id", nullable = false)
private Long parentId;
@Id
@Column(name = "omega_id", nullable = false)
private Long omegaId;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "parent_id", referencedColumnName = "id", nullable = false, insertable = false, updatable = false)
private Parent parent;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "omega_id", referencedColumnName = "id", nullable = false, insertable = false, updatable = false)
private Omega omega;
}
@Entity @Audited @Table(name = "omega"
public class Omega extends AuditEntity {
@Id
@GeneratedValue ....
private long id;
@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "beta", nullable = false)
private Beta beta;
}
@Entity @Audited @Table(name = "beta")
public class Beta extends AuditEntity {
@Id @Column
private long id;
@Column(nullable = false)
private Integer archived; // archived can be 0 or 1.
}
I want to run below query:
SELECT
p.id,
a.id,
o.id,
b.id, b.archived
FROM
parent p
LEFT JOIN alpha a ON a.parent_id = p.id
LEFT JOIN omega o ON a.omega_id = o.id
LEFT JOIN beta b ON o.beta_id = b.id AND d.archived = 0;
Intention is to get ids from parent, alpha & omega for which archived in beta is 0.
And for other records where beta is 1, it will be reported as null.
For this I've written below specification in Parent's repository, i.e. root is at Parent:
public static Specification<Parent> getParentWithActiveBeta() {
return (root, query, cb) -> {
Fetch<Object, Object> alpha = root.fetch("alpha", JoinType.LEFT);
Fetch<Object, Object> omega = alpha.fetch("omega", JoinType.LEFT);
Fetch<Object, Object> beta = omega.fetch("beta", JoinType.LEFT);
beta.on(cb.equal(beta.get("archived"), 0));
return null;
};
}
SQL query seems to be working fine but when I run specification, I get beta Ids for those records where archived is 1 as well.
I was expecting they will come as null.
What am I missing?