What's the deal with disjunction?

[Daryl Stultz]

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<ScheduledAssignment> 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?

Thanks.

/Daryl

[Daryl Stultz]

On Thursday, April 26, 2012 10:04:48 AM UTC-4, Daryl Stultz wrote:

Hello,

Shouldn't Ebean produce a left join for parentScheduledAssignment?

I've discovered that simply adding the left join doesn't get me the data I want, I had to use a raw() clause with unions and multiple self-joins. Maybe there's a way to solve my problem entirely with the API using subqueries, however my basic claim that a disjunction should produce a left join still stands.

/Daryl

[Rob Bygrave]

>> my basic claim that a disjunction should produce a left join still stands.

Yes. I see that. I'll log that as a Bug.

Cheers, Rob.

[corux]

Is there any progress on this issue?

I am also affected by this bug. We are using the Play Framework 2.1.1 with Ebean 3.1.2.

corux

On Tuesday, June 19, 2012 11:47:50 PM UTC+2, tkoeppen wrote:

+1
we are also affect by http://www.avaje.org/bugdetail-402.html in our Play Framework app (using play 2.1-SNAPSHOT).

Cheers,
Thomas

[Wolfgang Buchner]

currently not, this bug is still existing.

I think it would be a good start to repost this issue on the github repository:

https://github.com/ebean-orm/avaje-ebeanorm

Maybe you can use as a workaround the "where().raw()" feature with a subquery then or you can use a complete RawSQL query and write the Join by yourself.

[corux]

Thanks for the info. I submitted the issue on github: https://github.com/ebean-orm/avaje-ebeanorm/issues/37