Description Daniel Le Berre 2010-03-29 11:36:30 EDT

I got an entity like this representing a personn that can be tagged with interests. Such person has contacts that can themselves have interests.

@Entity
public class SocialEntity implements Serializable {

@ManyToMany(cascade = {CascadeType.PERSIST})
@OrderBy(value="name")
private List<Interest> interests = new ArrayList<Interest>();

@OneToMany
@JoinTable(name = "SOCIAL_ENTITY__CONTACTS")
private List<SocialEntity> contacts;
...
}

What I would like to know is "which are the interests owned by my contacts that I do not own myself ?".

It should be ok to write something like:

SELECT interest FROM SocialEntity soc ,IN(soc.contacts) contact, IN(contact.interests) interest
WHERE soc = :socialEntity AND interest NOT MEMBER OF soc.interests

The SQL translation produced by EclipseLink 2.0.1 is

SELECT t0.ID, t0.NAME, t0.PARENTINTEREST_ID FROM SOCIALENTITY_INTEREST t4, SOCIAL_ENTITY__CONTACTS t3, SOCIALENTITY t2, SOCIALENTITY t1, INTEREST t0 
WHERE (((? = t2.ID) AND NOT EXISTS (SELECT DISTINCT t6.ID FROM SOCIALENTITY_INTEREST t5, INTEREST t6 WHERE (((t5.entities_ID = t2.ID) AND (t6.ID = t5.interests_ID)) AND ((t4.entities_ID = t1.ID) AND (t0.ID = t4.interests_ID)))) ) AND (((t3.SocialEntity_ID = t2.ID) AND (t1.ID = t3.contacts_ID)) AND ((t4.entities_ID = t1.ID) AND (t0.ID = t4.interests_ID))))

Such query is wrong because the subquery returns a results as soon as there is one tuple in t5 (the remaining conditions are true because they are part of the outer query).

See discussion http://www.eclipse.org/forums/index.php?t=rview&goto=523825#msg_523825 for details.