Ordering collections in a many-to-many association

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 3.0.5

Mapping documents:
[code<class name="Group" table="MM_GROUPS">
<id name="id" column="id" type="int" unsaved-value="null" length="5">
<generator class="native" />
</id>
<property name="name" column="name" type="string" length="50" not-null="true" unique="false" />

<set name="members" table="MM_USERGROUPLINKS" inverse="false" order-by="name asc">
<key column="groupID" />
<many-to-many class="User" column="userID" />
</set>
</class>

<class name="User" table="MM_USERS" >
<id name="id" column="id" type="int" unsaved-value="null"
length="5">
<generator class="native" />
</id>
<property name="name" column="name" type="string" length="50" not-null="true" unique="false" />

<set name="memberships" table="MM_USERGROUPLINKS" inverse="false">
<key column="userID" />
<many-to-many class="Group" column="groupID" />
</set>
</class>[/code]

Code between sessionFactory.openSession() and session.close():

Code:

Query query = hbmSession.createQuery("select g from Group g");
            results = query.list();
            Iterator iter = results.iterator();
            while (iter.hasNext()) {
                group = (Group) iter.next();
                System.out.println("name: " + group.getName());
                
                Set members = group.getMembers();
                Iterator mIter = members.iterator();
                while (mIter.hasNext()) {
                    User u = (User) mIter.next();
                    System.out.println(u.getId() + " " + u.getName());
                }
            }

Full stack trace of any exception that occurs:

Code:

Jun 19, 2005 11:05:27 AM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 207, SQLState: 42S22
Jun 19, 2005 11:05:27 AM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Invalid column name 'name'.
org.hibernate.exception.SQLGrammarException: could not initialize a collection: 
[project.vo.Group.members#1]
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java
:43)
   at org.hibernate.loader.Loader.loadCollection(Loader.java:1441)
   at org.hibernate.loader.collection.CollectionLoader.initialize(CollectionLoader
.java:99)
   at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(Ab
stractCollectionPersister.java:488)
   at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializ
eCollection(DefaultInitializeCollectionEventListener.java:60)
   at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1430)
   at org.hibernate.collection.AbstractPersistentCollection.initialize(AbstractPer
sistentCollection.java:176)
   at org.hibernate.collection.AbstractPersistentCollection.read(AbstractPersisten
tCollection.java:48)
   at org.hibernate.collection.PersistentSet.iterator(PersistentSet.java:134)
   at HibernateTest.main(HibernateTest.java:50)
Caused by: java.sql.SQLException: Invalid column name 'name'.
   at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:367
)
   at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2606)
   at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2048)
   at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:574)
   at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:3
21)
   at net.sourceforge.jtds.jdbc.JtdsPreparedStatement.executeQuery(JtdsPreparedSta
tement.java:667)
   at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:120)
   at org.hibernate.loader.Loader.getResultSet(Loader.java:1272)
   at org.hibernate.loader.Loader.doQuery(Loader.java:391)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.ja
va:218)
   at org.hibernate.loader.Loader.loadCollection(Loader.java:1434)
   ... 8 more

Name and version of the database you are using: SQL Server 2000

The generated SQL (show_sql=true):

Code:

Hibernate: select group0_.id as id, group0_.name as name0_ from MM_GROUPS group0
_
name: POSC
Hibernate: select members0_.groupID as groupID1_, members0_.userID as userID1_, 
user1_.id as id0_, user1_.name as name2_0_ from MM_USERGROUPLINKS members0_ inne
r join MM_USERS user1_ on members0_.userID=user1_.id where members0_.groupID=? o
rder by members0_.name asc

As above, I have a many-to-many mapping between User and Group (one user can belong to many groups, and one groups can have many users). The many-to-many association is stored in MM_USERGROUPLINKS table.

What I want to achieve is for the "members" Set in Group to be ordered by the user's names whenever it is retrieved. That's why I used the order-by="name asc" attribute in the set mapping for members in Group.

However, the problem is that the set maps to MM_USERLINKS table, instead of MM_USERS table, and therefore Hibernate complains that it can't find the name field in MM_USERLINKS table.

In one-to-many associations, this has no problems at all, because the set maps to the actual table of the class, instead of to a bridging table.

Are there any way to go around this, short of using a native query?

Thanks for any advice!

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Hi,

Got some ideas on this, but may need some help with Section 17.5 Custom SQL for Loading in the reference.

The reference mentions that you can specify custom SQL for loading collections, however the documentation is not completed yet. It's denoted by a TODO: Document the following example for collection loader.
notice.

Has anyone explored the use of this? Would this help us here? I can see how we can sneak in the ordering of the collection via the custom SQL.

Also, another question is: when does this custom loading apply, all loading methods (e.g. session.load, session.get, session.createQuery which will return the collection), or just some specific methods?

Thanks in advance for any suggestions.

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Hi,

Have tried to use the custom loading as per my previous post to see if it's able to help load the set ordered.

Here's my mapping configuration:

Code:

<class name="Group" table="MM_GROUPS">
      <id name="id" column="id" type="int" unsaved-value="null" length="5">
         <generator class="native" />
      </id>
      <property name="name" column="name" type="string" length="50" not-null="true" unique="false" />
      <set name="members" table="MM_USERGROUPLINKS" inverse="false" lazy="true">
         <key column="groupID" />
         <many-to-many class="User" column="userID" />
         <loader query-ref="Group.sortMembers"/>
      </set>   
   </class>
<sql-query name="Group.sortMembers">
       <return alias="user" class="User"/>
       <synchronize table="MM_USERS"/>
       SELECT user1.id as {user.id}, user1.name as {user.name}
       FROM MM_GROUPS g, MM_USERS user1, MM_USERGROUPLINKS l where g.id=? and l.userID=user1.id and l.groupID=g.id
       order by user1.name
   </sql-query>

Here's the sql generated when I ran with this:

Code:

Hibernate: select group0_.id as id0_, group0_.name as name0_0_ from MM_GROUPS gr
oup0_ where group0_.id=?
Name of Group = POSC
Hibernate: SELECT user1.id as id0_, user1.name as name2_0_
       FROM MM_GROUPS g, MM_USERS user1, MM_USERGROUPLINKS l where g.id=? and l.us
erID=user1.id and l.groupID=g.id
       order by user1.name

I've verified that my native SQL does indeed return the users in the collection ordered by name.

This is the error that I got:

Code:

java.lang.NullPointerException
   at org.hibernate.loader.Loader.handleEmptyCollections(Loader.java:713)
   at org.hibernate.loader.Loader.doQuery(Loader.java:401)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.ja
va:218)
   at org.hibernate.loader.Loader.doList(Loader.java:1593)
   at org.hibernate.loader.Loader.list(Loader.java:1577)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
   at org.hibernate.persister.collection.NamedQueryCollectionInitializer.initializ
e(NamedQueryCollectionInitializer.java:49)
   at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(Ab
stractCollectionPersister.java:488)
   at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializ
eCollection(DefaultInitializeCollectionEventListener.java:60)
   at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1430)
   at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(Ab
stractPersistentCollection.java:280)
   at org.hibernate.Hibernate.initialize(Hibernate.java:293)
   at HibernateTest.main(HibernateTest.java:32)

Here's my HibernateTest.java:

Code:

Session hbmSession = HibernateUtil.getSession();
            HibernateUtil.beginTransaction();
            
            Group g = new Group();
            g = (Group) hbmSession.get(g.getClass(), new Integer(1));
            System.out.println("Name of Group = " + g.getName());
            [b]Hibernate.initialize(g.getMembers());[/b]
            Iterator iter = g.getMembers().iterator();
            while (iter.hasNext()) {
                User s = (User) iter.next();
                System.out.println(s.getId() + " " + s.getName());
            }
            
            HibernateUtil.commitTransaction();
            HibernateUtil.closeSession();
            System.out.println("Success!");

Line 32, which is the line in bold was the line causing the error. Seems like a lazy collection initialization issue.

Any ideas? We are very close to a solution here!

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Hi,

Tried to run the sql query manually using HibernateTest.java and the session.getNamedQuery method. Got the following errors:

Code:

Hibernate: SELECT user1.id as id, user1.name as name
       FROM MM_USERS user1 where user1.id in (select userID from MM_USERGROUPLINKS
 where groupID=?)
       order by user1.name
Jun 19, 2005 11:17:29 PM org.hibernate.util.JDBCExceptionReporter logExceptions
WARNING: SQL Error: 0, SQLState: 07009
Jun 19, 2005 11:17:29 PM org.hibernate.util.JDBCExceptionReporter logExceptions
SEVERE: Invalid column name clazz_0_.
org.hibernate.exception.SQLGrammarException: could not execute query
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:59)
   at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java
:43)
   at org.hibernate.loader.Loader.doList(Loader.java:1596)
   at org.hibernate.loader.Loader.list(Loader.java:1577)
   at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:112)
   at org.hibernate.impl.SessionImpl.listCustomQuery(SessionImpl.java:1414)
   at org.hibernate.impl.SQLQueryImpl.list(SQLQueryImpl.java:153)
   at HibernateTest.main(HibernateTest.java:31)
Caused by: java.sql.SQLException: Invalid column name clazz_0_.
   at net.sourceforge.jtds.jdbc.JtdsResultSet.findColumn(JtdsResultSet.java:885)
   at net.sourceforge.jtds.jdbc.JtdsResultSet.getInt(JtdsResultSet.java:889)
   at org.hibernate.type.IntegerType.get(IntegerType.java:26)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:77)
   at org.hibernate.type.NullableType.nullSafeGet(NullableType.java:94)
   at org.hibernate.loader.Loader.getInstanceClass(Loader.java:1035)
   at org.hibernate.loader.Loader.instanceNotYetLoaded(Loader.java:924)
   at org.hibernate.loader.Loader.getRow(Loader.java:855)
   at org.hibernate.loader.Loader.getRowFromResultSet(Loader.java:305)
   at org.hibernate.loader.Loader.doQuery(Loader.java:412)
   at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.ja
va:218)
   at org.hibernate.loader.Loader.doList(Loader.java:1593)
   ... 5 more

Any ideas what migh be the problem here? I think we're through once we solve this :-)

joeserel · **Joined:** Thu Mar 03, 2005 9:19 pm **Posts:** 22

you can make hibernate do the sort for your many-to-many.

but you have to write a link model - not let hibernate generates it for you - one example is in the caveatemptor sample - CategorizedItem class.

joeserel · **Joined:** Thu Mar 03, 2005 9:19 pm **Posts:** 22

you can also sort by yourself in app layer - but you have to use a List instead of hashset. TreeSet is ok too, I think.

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Hi josereel,

I can't use list or any indexed collections because in a many to many association, the tuples are stored in a linking table where there aren't any indexes, and which will not contain the fields you want to sort by.

So we are trying to sort a collection (set) by a field that is NOT in the table that the set references.

Not sure what a "links model" is, but I'll follow up on the caveatemptor and see if it help.

Thanks so much for the suggestions!

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Hi josereel,

Have checked out the caveat-emptor example that you were referring to. Seems like what they did there was to convert the many-to-many relationship into 2 many-to-one relationships with a linking class.

They can do this because there are additional fields in the linking table, and those fields happen to be the ones that they would want to sort by.

In our case here, if we were to do this, I would need to put both user and group names into the MM_USERGROUP_LINKS table, since those are the fields I might want to sort by.

That would result in
1. A poor database design where the user and group names are duplicated in 2 tables each.

2. Complications in updating the links table to maintain sychronisation of the names with the MM_USERS and MM_GROUPS table. Creating an id-name composite key for both User and Group might alleviate this a bit, but would be a most unwieldy solution.

I'm just surprised that Hibernate is not able to take care of such a commonly occurring case, despite its amazing flexibility.
I think such a many-to-many relationship is common in role-based systems involving users and groups, despite the comment in the Hibernate reference that many-to-many relationships are rare, and should be used judiciously.

I think there is still hope in the custom loading query that I was working on, though I would need some Hibernate expertise on this particular topic which was not fully documented in the reference for 3.0.5.

Also I noticed that the caveat emptor example is for Hiberante 2.1.6, whereas I'm on Hibernate 3.0.5 here, so that might be something new in Hibernate 3.0.5 that I don't know about yet that might help.

Wonder if any member of the Hibernate team has got any suggestions on this challenge? :-)

joeserel · **Joined:** Thu Mar 03, 2005 9:19 pm **Posts:** 22

no - that is not poor db design and i think it can do what you want...

The linked model (MM_USERGROUP_LINKS) will generates the same table hibernate generates for you. It could have three columns userId, groupId and createDt (taht is, if you want creatDt). You do not put username or other fields in it. Instead,
In the MM_USERGROUP_LINKS pojo, you have two objects - User user and Group group (many-to-one)and that is where you can sort on - you can sort on any field of User or Group.

basically, you have a collection of MM_USERGROUP_LINKS in Group pojo,
then in the order-by cluse, you do lazy="false" and order-by="user.userName" . Then run the query "from Group" it will give you a collection of MM_USERGROUP_LINKS which is sorted on userName - remember MM_USERGROUP_LINKS also has group instance so you also have a set of group sorted by userName.

joeserel · **Joined:** Thu Mar 03, 2005 9:19 pm **Posts:** 22

sorry for the last sentence I meant "user" instance. So, you get a group with a users collection sorted by userName.

everbright · **Joined:** Wed Jun 15, 2005 2:00 pm **Posts:** 38

Hi josereel,

I tried what you suggested, but it didn't work either, as I expected. Got the same error: user.userName is not a valid field.

The thing is you can't specify order-by="user.userName", because the value for order-by attribute must be an SQL expression, not a Hibernate query expression.

The problem is that the set (for users) in Group references the links table, and therefore our order-by attribute has to be a valid field in the links table, and "user.userName" is simply not valid in the links table. This is very clear from the SQL generated by Hibernate.

Here's the configuration I used:

Code:

<class name="UserGroupLink" table="MM_USERGROUPLINKS">
      <composite-id name="id" class="UserGroupLinkID" unsaved-value="any">
         <key-property name="userID" column="userID" length="16"/>
         <key-property name="groupID" column="groupID" length="16"/>
      </composite-id>
      <many-to-one name="user"  class="User" not-null="true" column="userID" insert="false" update="false"/>
      <many-to-one name="group" class="Group" not-null="true" column="groupID" insert="false" update="false"/>
   </class>
   
   <class name="User" table="MM_USERS">
      <id name="id" column="id" type="int" unsaved-value="null" length="5">
         <generator class="native"/>
      </id>
      <property name="name" column="name" type="string" length="50" not-null="true"/>
      <set name="memberships" cascade="all-delete-orphan" inverse="true" outer-join="false" order-by="group.name" lazy="false">
         <key column="userID" not-null="true" />
         <one-to-many class="UserGroupLink"/>
      </set>
   </class>
   
   <class name="Group" table="MM_GROUPS">
      <id name="id" column="id" type="int" unsaved-value="null" length="5">
         <generator class="native"/>
      </id>
      <property name="name" column="name" type="string" length="50" not-null="true"/>
      <set name="members" cascade="all-delete-orphan" inverse="true" outer-join="false" order-by="user.name" lazy="false">
         <key column="groupID" not-null="true" />
         <one-to-many class="UserGroupLink"/>
      </set>
   </class>

I generated the POJOs and database using Hibernate tools.

Here's what I got when I tried to instantiate a Group and initialize its set of members.

Code:

INFO: Error performing load command
org.hibernate.exception.GenericJDBCException: could not initialize a collection:
 [project.vo.Group.members#1]
   at org.hibernate.exception.SQLStateConverter.handledNonSpecificException(SQLSta
teConverter.java:82)
   at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:70)
   at org.hibernate.exceHibernate: select members0_.groupID as groupID1_, members0
_.userID as userID1_, members0_.userID as userID0_, members0_.groupID as groupID
0_, members0_.userID as userID0_0_, members0_.groupID as groupID0_0_ from MM_USE
RGROUPLINKS members0_ where members0_.groupID=? order by members0_.user.name
ption.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
   at org.hibernate.loader.Loader.loadCollection(Loader.java:1441)
   at org.hibernate.loader.collection.OneToManyLoader.initialize(OneToManyLoader.j
ava:111)
   at org.hibernate.persister.collection.AbstractCollectionPersister.initialize(Ab
stractCollectionPersister.java:488)
   at org.hibernate.event.def.DefaultInitializeCollectionEventListener.onInitializ
eCollection(DefaultInitializeCollectionEventListener.java:60)
   at org.hibernate.impl.SessionImpl.initializeCollection(SessionImpl.java:1430)
   at org.hibernate.collection.AbstractPersistentCollection.forceInitialization(Ab
stractPersistentCollection.java:280)

Note the SQL generated. It only selects from one table MM_USERGROUPLINKS, so how can we expect it to be able to successfully sort against a field in the MM_USERS table??

Or am I missng something here??

joeserel · **Joined:** Thu Mar 03, 2005 9:19 pm **Posts:** 22

Then try createFilter on the collection - that should do the trick.
http://www.hibernate.org/hib_docs/v3/re ... -filtering

rfulcher · **Joined:** Tue Mar 29, 2005 5:34 pm **Posts:** 19

everbright did you ever get a solution to this issue. I have the same problem and don't know how to solve it.

Any help would be greatly appreciated.

Thanks

RahulJoshi · **Posted:** Tue Sep 13, 2005 3:36 am

hi all,

I am trying to perform a many-to-many mapping
for the following tables
---------
table A
----------
A_ID PRIMARY KEY
..
..
---------------------------

---------
table B
---------
B_ID PRIMARY KEY
...
...
--------------------------------------

----------
table C
-----------
A_ID PRIMARY KEY
B_ID PRIMARY KEY
date
----------------------

where table C is a product of many-to-may relationship between A and B
What mapping should I write to maintain this relationship alongwith an additional field in table C "date" which is not an index.