Hello,

Hello,

I have "Case" with child collection "ScheduledAssignment". The following query:

ExecutionResources.getEbeanServer().find(Case.class)
.fetch("scheduledAssignments")
.where()
.eq("scheduleDay.id", 4205)
.findList();

Produces this SQL:

select t0.caseid as c0, t0.externalId as c1, ...
from public.cases t0
join public.locations t2 on t2.locationid = t0.roomlocationid
left outer join public.scheduledassignments t1 on t1.caseId = t0.caseid
left outer join public.roles t3 on t3.roleid = t1.roleid
where t0.scheduledayid = ?
order by t0.caseid

Since any given Case may or may not have children, the left join is appropriate. If I add a FetchConfig:

ExecutionResources.getEbeanServer().find(Case.class)
.fetch("scheduledAssignments", new FetchConfig().query())
.where()
.eq("scheduleDay.id", 4205)
.findList();

I get 2 queries:

select t0.caseid as c0, t0.externalId as c1, ...
from public.cases t0
join public.locations t1 on t1.locationid = t0.roomlocationid
where t0.scheduledayid = ?

select t0.caseid as c0, t1.scheduledassignmentid as c1, ...
from public.cases t0
left outer join public.scheduledassignments t1 on t1.caseId = t0.caseid
left outer join public.roles t2 on t2.roleid = t1.roleid
where t0.caseid in (?,?,?,?,? ...)
order by t0.caseid

Note that the second query selects from cases and left joins scheduledassignments. This is unexpected. It seems it should select from scheduledassignments where scheduledassignments.caseid in (?, ? ...) (or simply inner join cases to scheduledassignments). The left joins here are significant in terms of degrading performance. Is there some way to tune this to get inner joins instead? Or is there some architectural reason to require at least one record per case?

Thanks.