Hello,

I have ScheduledAssignment such that it has a foreign key to itself. IOW with "linked" instances A, B, C of ScheduledAssignment:

A_B_C

B and C have "parentScheduledAssignment" of A (a foreign key).

This simple query attempts to pull in the other related objects, so given B I should get [A, C] and given A I should get [B, C]:

query.where()
.disjunction()
.eq("parentScheduledAssignment", scheduledAssignment) // SA is child of this one
.eq("childScheduledAssignments.id", scheduledAssignment.getId()) // or parent of this one
.endJunction()
.ne(ScheduledAssignment.pId, scheduledAssignment.getId()) // not this one
.findList();

But this is the SQL produced:

select distinct [a bunch of stuff]
from public.scheduledassignments t0
join public.scheduledassignments u1 on u1.parentscheduledassignmentid = t0.scheduledassignmentid
where (
t0.parentscheduledassignmentid = ?
or u1.scheduledassignmentid = ?
)
and t0.scheduledassignmentid <> ?

Notice the regular join u1. The result is an empty list.

The relevant part of ScheduledAssignment:

@Entity
@Table(name = "scheduledassignments", schema = "public")
public class ScheduledAssignment {

@Id
@Column(name = "scheduledassignmentid")
private Integer id;
@ManyToOne
@JoinColumn(name = "parentscheduledassignment")
private ScheduledAssignment parentScheduledAssignment;
@OneToMany(mappedBy = "parentScheduledAssignment")
private List childScheduledAssignments;
}

Shouldn't Ebean produce a left join for parentScheduledAssignment? If I remove the where criteria and "fetch" parentScheduledAssignment properties it does in fact produce a left join. This seems a pretty common scenario. How do I solve this?