hql on many-to-many returns multiples of the same object

wpfeiffe · **Posted:** Thu May 12, 2005 10:50 pm

I have a fairly standard unidirectional many-to-many relationship between a user table and a role table. I am attempting to retrieve a specific user and the roles for that user.

Any ideas why the following hql query would return me a collection of 4 of same OpUser object? Note that this particular user has four roles in the db. I tried using "distinct" to no effect.

Is there a better way? I tried getting just the user with no join and doing Hibernate.initialize() but it only initialized the collection, not the contents (just as the docs state).

Code:
------

Code:

// get the list of OpUsers for given loginId (should only be 1)
Query q = session.createQuery("from OpUser as opuser " +
      " join fetch opuser.roles as roles " +
      " where opuser.loginId = :loginId");
q.setString("loginId", loginId);
users = q.list();

// if users found and there is only 1 (THIS FAILS)
if (users != null && users.size() == 1)
{
   // get the user
   foundUser = users.get(0);
   //Hibernate.initialize(foundUser.getRoles());

}

Config:
----
using JBoss 4.0.2 and Hibernate (3.0.1 comes w/ this JBoss version I think)

Relevent mapping:

Code:

<class name="OpUser" table="op_user">
    <id name="userId"
        type="java.lang.Integer" column="user_id">
        <generator class="assigned" />
    </id>
    <property name="loginId"
        type="java.lang.String" column="login_id"
        not-null="true" length="30" />
    ...
    <set name="roles" table="user_role"
        lazy="true" cascade="none">
        <key> <column name="user_id" />  </key>
        <many-to-many class="OpRole" column="role_id" />
    </set>
</class>   

<class name="OpRole" table="op_role">
    <id name="roleId" type="java.lang.Integer"
        column="role_id">
        <generator class="sequence">
         <param name="sequence">op_role_role_id_seq</param>
      </generator>
    </id>
    ...
</class>

wpfeiffe · **Posted:** Mon May 16, 2005 5:18 pm

If anyone is interested (the underwhelming response indicates that no-one is), I found an answer in the Hibernate in Action book. Hopefully no-one will bust my chops for posting this:

Code:

If you fetch a collection, Hibernate doesn’t return a distinct result list. For example,
an individual Item might appear several times in the result List, if you
outer-join fetch the bids. You’ll probably need to make the results distinct
yourself using, for example: distinctResults = new HashSet(resultList);.
A Set doesn’t allow duplicate elements.

I guess there's a 'GOOD (tm)' reason for this non-distinct collection of the one object. But if, when you select 1 object that has a collection associated, you expect 1 object (craziness!) returned, you must distinctify it with a HashSet.

Yeroc · **Joined:** Tue Mar 15, 2005 1:38 pm **Posts:** 11

I just ran into this problem myself and I fail to understand what the good reason for this. At a minimum, I would expect the default behavior to return the distinct objects.

wpfeiffe wrote:

I guess there's a 'GOOD (tm)' reason for this non-distinct collection of the one object. But if, when you select 1 object that has a collection associated, you expect 1 object (craziness!) returned, you must distinctify it with a HashSet.

There are two different answers to the same question in two different FAQs on the website currently:

1) http://www.hibernate.org/117.html#A11 which states:

Quote:

Query result lists are always exactly the same size as the underlying JDBC ResultSet. ...

without providing a reason for this (limitation in my view.)

2) http://www.hibernate.org/74.html#A7 which says:

Quote:

It's intentional as that's how the semantics are for joins in the relational algebra. Distinct your parent objects in your java code.

So supposedly the good reason is to preserve the semantics for joins in relational algebra? I don't understand why you would want to preserve these semantics in an O/R tool... :P

Can someone enlighten me?

Corey

wpfeiffe · **Posted:** Wed May 18, 2005 9:09 pm

Thanks for the response. Seems odd to preserve the semantics of a resultset into an object model. Knowing what its doing helps out though.

Thanks again,