Many-to-many querying using Criteria API

erizzo · **Joined:** Wed Oct 26, 2005 11:55 pm **Posts:** 14

Hibernate version: 3.1.3
Name and version of the database you are using: MySQL 5.0

Mapping documents:

Code:

<class name="Document" table="documents">
   <set name="authors" table="authors" cascade="all">
      <key column="document_ID" not-null="true" />
      <many-to-many column="person_ID" class="Person"  />
   </set>

   <set name="editors" table="editors" cascade="all">
      <key column="document_ID" not-null="true" />
      <many-to-many column="person_ID" class="Person"  />
   </set>

   <set name="publishers" table="publishers" cascade="all">
      <key column="document_ID" not-null="true" />
      <many-to-many column="person_ID" class="Person"  />
   </set>
</class>

I have a situation where my primary entity, Document, has several many-to-many relations to other tables (Document object contains several Sets: Authors, Publishers, Editors). Each of those Sets contains instances of a Person entity. Any Person can be in any of the three Sets of any Document.
Given a particular Person, I need a query that will return all Documents that have that Person in either the Authors, Publishers, or Editors sets.

I've tried the following, which does not work (it returns no results):

Code:

session.createCriteria(Document.class)
    .createAlias("authors", "a")
    .createAlias("editors", "e")
    .createAlias("publishers", "p")
    .add(Restrictions.disjunction()
            .add(Restrictions.eq("a.id" idIAmLookingFor))
            .add(Restrictions.eq("e.id" idIAmLookingFor))
            .add(Restrictions.eq("p.id" idIAmLookingFor))
        );

Interestingly, the following does work (if I only try to join to the Authors set instead of all three):

Code:

session.createCriteria(Document.class)
    .createAlias("authors", "a")
    .add(Restrictions.eq("a.id" idIAmLookingFor));

Here is the HQL that does what I want, but I need to find a solution using Criteria API so I can combine it with QBE:

Code:

from Document
where :personID in elements(authors)
or :personID in elements(editors)
or :personID in elements(publishers)

I've been through the reference docs several times, but can't seem to find an example that does this kind of joining via Criteria API.
What am I doing wrong? Or where in the docs can I find examples of this? I'm willing to do my own research, just need some guidance.

Thanks in advance,
Eric

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

Your first attempt (three aliases and a disjunction) will work, but only for documents that have at least one author, editor and publisher: it inner joins to all three tables. I haven't found a way to left join when calling createAlias: maybe createCriteria would help? I'd use three criteria for this.

If you absolutely have to get this working, you could try a workaround that I used a couple of years ago: require that all relevant sets be non-empty. In your case, you'd create a Person called "Unknown", and ensure that every set of Persons includes Unknown (unless it includes real Persons). Then the inner joins would always result in at least one row.

erizzo · **Joined:** Wed Oct 26, 2005 11:55 pm **Posts:** 14

tenwit wrote:

Your first attempt (three aliases and a disjunction) will work, but only for documents that have at least one author, editor and publisher: it inner joins to all three tables. I haven't found a way to left join when calling createAlias: maybe createCriteria would help? I'd use three criteria for this.

Thanks for the hint! I never realized that createAlias() uses a inner join. I was able to use the overloaded form,

Code:

createAlias(String path, String alias, int joinType)

, which seems to work.

So now my code looks like this:

Code:

session.createCriteria(Document.class)
    .createAlias("authors", "a", Criteria.LEFT_JOIN)
    .createAlias("editors", "e", Criteria.LEFT_JOIN)
    .createAlias("publishers", "p", Criteria.LEFT_JOIN)
    .add(Restrictions.disjunction()
            .add(Restrictions.eq("a.id" idIAmLookingFor))
            .add(Restrictions.eq("e.id" idIAmLookingFor))
            .add(Restrictions.eq("p.id" idIAmLookingFor))
        );

As I said, this seems to work but it sure is ugly! I can't help but think there must be a shorter distance from here to there.

You mentioned using three Criteria - can you elaborate? I'm reviewing the Criterion, Criteria, Restrictions, etc. API docs but can't quite get the gist of what you were thinking.

Thanks,
Eric

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

I meant that you'd build three queries: one for each join. Then you'd merge the results. However, if there's a way to choose the join type (there isn't in H3.1.1, which I'm using), then you should use that. It's not ugly, imo.

erizzo · **Joined:** Wed Oct 26, 2005 11:55 pm **Posts:** 14

erizzo wrote:

So now my code looks like this:

Code:

session.createCriteria(Document.class)
    .createAlias("authors", "a", Criteria.LEFT_JOIN)
    .createAlias("editors", "e", Criteria.LEFT_JOIN)
    .createAlias("publishers", "p", Criteria.LEFT_JOIN)
    .add(Restrictions.disjunction()
            .add(Restrictions.eq("a.id" idIAmLookingFor))
            .add(Restrictions.eq("e.id" idIAmLookingFor))
            .add(Restrictions.eq("p.id" idIAmLookingFor))
        );

The above works for the most part, but has one problem. Some queries are returning the same record more than once. Any ideas why that would be happening?

Thanks again,
Eric

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

You're using the default result transformer. If you want just one of each Document, use setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).

erizzo · **Joined:** Wed Oct 26, 2005 11:55 pm **Posts:** 14

tenwit wrote:

You're using the default result transformer. If you want just one of each Document, use setResultTransformer(Criteria.DISTINCT_ROOT_ENTITY).

That did the trick, thanks!
I appreciate your help. If you happen to have a few minutes more...
The documentation on ResultTransformers is almost non-existent. I'd like to understand exactly what I'm doing in code, so if you can give an overview or pointer to better material than the Hibernate Reference docs, I'd really appreciate it. I just don't like having code that I do not understand, and I'd like to learn as much as possible about ResultTransformers so that I can understand where it might be appropriate to apply them elsewhere in my work.

Thanks,
Eric

tenwit · **Joined:** Thu Dec 23, 2004 9:08 pm **Posts:** 2008

The best docs I've found for it are in the javadocs for the CriteriaSpecification interface. AliasToBeanResultTransformer isn't mentioned in there, but I don't know what it does, so I can't help you with that one.

Don't forget to rate!