Hibernate left join constraint in a many-to-many relationship

Question

I have built a list of taggable documents, with a many-to-many relationship between the tags and the documents. I would now like to use the hibernate criteria mechanism to query a "summary" of each tag, which includes a count of how often a particular tag has been used, with an additional restriction on whether or not the document has been published.

The entities I'm using roughly look like this (You'll note an SQL join table in the middle there):

@Entity
public class DocumentTag {

    ... various things ...

    @ManyToMany(fetch = FetchType.LAZY, mappedBy = "tags")
    private List<Document> documents = new ArrayList<>();

}

@Entity
public class Document {

    ... various things ...

    @Basic
    @Column(name = "published", columnDefinition = "BIT", length = 1)
    protected boolean published = false;

    @ManyToMany(fetch = FetchType.LAZY)
    @JoinTable(name = "document_tag_joins",
            uniqueConstraints = @UniqueConstraint(
                    columnNames = {"document", "tag"}
            ),
            joinColumns = {@JoinColumn(name = "document")},
            inverseJoinColumns = {@JoinColumn(name = "tag")})
    private List<DocumentTag> tags = new ArrayList<>();

}

Given the above, I've managed to figure out that building the query should work more or less as follows:

Criteria c = session.createCriteria(DocumentTag.class);

c.createAlias("documents", "docs",
        JoinType.LEFT_OUTER_JOIN,
        Restrictions.eq("published", true)
);

c.setProjection(
        Projections.projectionList()
                .add(Projections.alias(Projections.groupProperty("id"), "id"))
                .add(Projections.alias(Projections.property("createdDate"), "createdDate"))
                .add(Projections.alias(Projections.property("modifiedDate"), "modifiedDate"))
                .add(Projections.alias(Projections.property("name"), "name"))
                .add(Projections.countDistinct("docs.id"), "documentCount"));

// Custom response entity mapping
c.setResultTransformer(
        Transformers.aliasToBean(DocumentTagSummary.class)
);

List<DocumentTagSummary> results = c.list();

Given the above, the hibernate generated SQL query looks as follows:

SELECT
  this_.id                AS y0_,
  this_.createdDate       AS y1_,
  this_.modifiedDate      AS y2_,
  this_.name              AS y3_,
  count(DISTINCT doc1_.id) AS y5_
FROM tags this_ 
  LEFT OUTER JOIN tag_joins documents3_
    ON this_.id = documents3_.tag AND (doc1_.published = ?)
  LEFT OUTER JOIN documents doc1_
    ON documents3_.document = doc1_.id AND (doc1_.published = ?)
GROUP BY this_.id

As you can see above, the publishing constraint is applied to both of the left outer joins. I'm not certain whether that is by design, however what I need is for the published constraint to be applied ONLY to the second left outer join.

Any ideas?

score 0 · Answer 1 · answered Aug 27 '15 at 14:53

I was able to circumvent this problem by coming at it sideways. First, I had to change the "published" column to use an integer rather than a bit. Then I was able to slightly modify the projection of the result as follows:

    // Start building the projections
    ProjectionList projections =
            Projections.projectionList()
                    .add(Projections.alias(
                            Projections.groupProperty("id"), "id"))
                    .add(Projections.alias(
                            Projections.property("createdDate"),
                            "createdDate"))
                    .add(Projections.alias(
                            Projections.property("modifiedDate"),
                            "modifiedDate"))
                    .add(Projections.alias(
                            Projections.property("name"), "name"));

    if (isAdmin()) {
        // Give the raw count.
        projections.add(Projections.countDistinct("docs.id"), "documentCount");
    } else {
        // Use the sum of the "published" field.
        projections.add(Projections.sum("docs.published"), "documentCount");
    }

I acknowledge that this doesn't actually answer the question about why hibernate criteria constraints on many-to-many tables get applied to all tables, but it solved my problem.

Hibernate left join constraint in a many-to-many relationship

1 Answers1