JPQL left outer join on many to many relationship

Question

I'm trying to write a query (using JPQL) to get all tags and how often each has been referenced by all items. The JPQL is:

SELECT t.id, t.name, SIZE(t.items) FROM Tag t GROUP BY t.id, t.name ORDER BY t.name ASC

Note that some tags are not referenced by any items, but I still want them included in the results (and expecting SIZE(t.items) would be zero).

However, when this query is executed, it returns only the tags that have items associated with it, ignoring tags that are not referenced by any items. The generated SQL from JPA is:

SELECT t0.id as a1, t0.NAME AS a2, COUNT(t1.id) FROM tag t0, item_tag_map t2, item t1 WHERE ((t2.tag_id = t0.id) AND (t1.id = t2.item_id)) GROUP BY t0.id, t0.NAME ORDER BY t0.NAME ASC;

It is not performing a LEFT OUTER JOIN which is required to get the results I want. If I was writing this in SQL, I would have done something like

select t.id, t.name, count(map.item_id) from tag as t left join item_tag_map as map on map.tag_id = t.id group by t.id, t.name order by t.name ASC;

Is there any way to accomplish this in JPQL? Am I doing something wrong or is it a limitation of JPQL (or a bug)? I'm using PostgreSQL v9.2 and EclipseLink v2.4.2

To provide more details... I have 3 SQL tables: item, tag, and item_tag_map. And here are the snippet of the related Java classes:

@Entity
@Table(name="item")
public class Item implements Serializable {
    @Id
    @Column(name="id", updatable=false)
    private String id;

    @ManyToMany(cascade=CascadeType.ALL)
    @JoinTable(
        name = "item_tag_map", 
        joinColumns = { @JoinColumn(name = "item_id", referencedColumnName = "id", nullable=false) }, 
        inverseJoinColumns = { @JoinColumn(name = "tag_id", referencedColumnName = "id", nullable=false) })
    private List<Tag> tags;
...


@Entity
@Table(name="tag")
@NamedQueries({
    @NamedQuery(name="Tag.findAllStats", query="SELECT t.id, t.name, SIZE(t.items) FROM Tag t GROUP BY t.id, t.name ORDER BY t.name ASC"),
})
public class Tag implements Serializable {
    @Id
    @Column(name="id", updatable=false)
    private long id;

    private String name;

    @ManyToMany(mappedBy="tags", fetch=FetchType.LAZY)
    private List<Item> items;
...

Answer 1

I'm not sure if it's an EclipseLink bug or not (it looks like it is one to me, though), but you could probably use the following query to solve the problem (not tested though):

select t.id, t.name, count(i.id) from Tag t 
left join t.items i
group by t.id, t.name 
order by t.name asc