JPQL query "NOT MEMBER OF" using API criteria

Question

Given the following annotated JPA entity classes:

@Entity @Table("foo") public class Foo { @Id private int id; @Column(name="name") private String name; @ManyToMany @JoinTable(name = "foo_tags", joinColumns = {@JoinColumn(name = "foo")}, inverseJoinColumns = {@JoinColumn(name = "tag")}) private Collection<Tag> tags; ... } @Entity @Table(name = "tag") public class Tag { @Id private String tag; ... } 

I am trying to formulate a query to get all instances of Foo that lack this tag. The following JPQL query does the trick

 SELECT f FROM Foo f WHERE :tag NOT MEMBER OF f.tags 

However, it’s difficult for me to translate this into a query for criteria. The translation seems obvious (to me):

 CriteriaBuilder cb = em.getCriteriaBuilder(); CriteriaQuery<Foo> query = cb.createQuery(Foo.class); Root<Foo> from = query.from(Foo.class); query.where(cb.isNotMember(cb.parameter(Tag.class, "tag"), from.get(Foo_.tags))); TypedQuery<Foo> tq = em.createQuery(query); return tq.setParameter("tag", sometag).getResultList(); 

Generated SQL is significantly different in these cases. The first request generates the following:

 SELECT t0.id, t0.name FROM foo t0 WHERE NOT EXISTS ( SELECT DISTINCT t2.TAG FROM tag t2, foo_tags t1 WHERE (((t1.foo = t0.id) AND (t2.TAG = t1.tag)) AND ('blue' = t2.TAG))) 

while a criteria request generates this:

 SELECT t1.id, t1.name FROM tag t0, foo_tags t2, Foo t1 WHERE (NOT ((t0.TAG = 'blue')) AND ((t2.foo = t1.id) AND (t0.TAG = t2.tag))) 

I only tested this with the eclipselink implementation, so maybe there was a problem there, but thought I'd ask here first if anyone sees an obvious error.

Answer 1

SQL should be the same, although both seem like they will work (minus the empty case).

Please report the error in the criteria question in EclipseLink.

You should be able to use a join or optional selection instead of a special syntax member.

 SELECT f FROM Foo f left join f.tags t WHERE not(t == :tag)