Databases
StackOverflow2013
Postsdbo
POJPQL left outer join on many to many relationship
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJPQL left outer join on many to many relationship
    primarykey
    data
    text
    <p>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:</p> <pre><code>SELECT t.id, t.name, SIZE(t.items) FROM Tag t GROUP BY t.id, t.name ORDER BY t.name ASC </code></pre> <p>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). </p> <p>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:</p> <pre><code>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; </code></pre> <p>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</p> <pre><code>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; </code></pre> <p>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</p> <p>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:</p> <pre><code>@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&lt;Tag&gt; 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&lt;Item&gt; items; ... </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. This table or related slice is empty.
    1. USSimon Arsenault
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POJPQL left outer join on many to many relationship
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload