Hibernate version:
2.1.3

Mapping documents:
<hibernate-mapping package="com.peerimpact.contentstorage">

<class name="CreativeCredit" table="CREDIT">

<id name="id" unsaved-value="0" column="credit_id">
<generator class="native"/>
</id>

<property name="name">
<column name="credit_name" index="idx_creditname" not-null="true"/>
</property>

<set name="albums" table="CREDIT_ALBUM" inverse="true" lazy="true" order-by="PUBLISH_DATE desc">
<key>
<column name="credit_id" not-null="true"/>
</key>
<many-to-many class="Album">
<column name="album_id" not-null="true"/>
</many-to-many>
</set>

</class>

</hibernate-mapping>



<hibernate-mapping package="com.peerimpact.contentstorage">

<class name="Album" table="ALBUM">

<id name="albumId" unsaved-value="0" column="album_id">
<generator class="native"/>
</id>
....more proerties....
<property name="publishDate" type="date">
<column name="publish_date" index="idx_albumpublishdate"/>
</property>
</class>

</hibernate-mapping>
Name and version of the database you are using:
MS SQL Server 2000

Running under JBoss 3.2.5

Question:
This mapping file holds an artist's information. There is a set of albums that are mapped to the artist through a crosswalk table named credit_album. When I load up the CreativeCredit object, I would like to have the albums in its album set sorted by publish date. I put the entry that I'm using right now in bold in the mapping file, it doesn't seem to be working. I tried using the name of the set in the class itself, and also the name in the database.

Is this something that can be done? Am I just specifying the sort the wrong way?

In the order-by attribute, I've tried using the field name (publishDate), using the field name qualified with the set name (albums.publishDate), and the database field name(PUBLISH_DATE). Am I just referencing the field wrong?

The weird thing that happens though is that they are being sorted now, it's just not anything I can decipher. Before I put in the order-by statement, they'd come back randomly. Now they're coming back in the same order every time, just NOT sorted by publish date.

Again, any help is appreciated....thank you!

It would help a lot to see the generated sql. Use configuration option show_sql=true.

Also, what is the datatype of PUBLISH_DATE in the DB? One thing that comes to my mind is that
in SQL Server 2000 the sorting order depends on the collation setting in use, so could it be that you
have a field where the date is written as a string, and a collation which sorts it funnily?

t: Ville Peurala

Publish_date is a datetime field in the database.

And it looks like this is the query that is executing, problem is that the order-by doesn't show up anywhere!!

select this.credit_id as credit_id1_, this.credit_name as credit_n2_1_, albums1_.album_id as album_id__,
albums1_.credit_id as credit_id__, album2_.album_id as album_id0_, album2_.title astitle0_, album2_.version_title as version_3_0_,
album2_.duration as duration0_, album2_.notes as notes0_, album2_.is_compilation as is_compi6_0_,
album2_.publisher as publisher0_, album2_.publish_date as publish_8_0_, album2_.incomplete asincomplete0_,
album2_.explicit_lyrics as explici10_0_, album2_.artwork_copyright as artwork11_0_, album2_.upc as upc0_,
album2_.genre_id as genre_id0_, album2_.amg_rating as amg_rating0_, album2_.amg_best_of_rating as amg_bes15_0_,
album2_.amg_best_of_pick as amg_bes16_0_, album2_.cover_graphic_id as cover_g17_0_, album2_.product_id as product_id0_
from CREDIT this
left outer join CREDIT_ALBUM albums1_ on this.credit_id=albums1_.credit_id
left outer join ALBUM album2_ on albums1_.album_id=album2_.album_id
where this.credit_id=?

You didn't tell that you are using an outer join. What is the HQL that you're executing?
Attribute 'order-by' only affects the iteration order of the set itself, so the set is not automatically ordered when retrieved as a part of some bigger query.

I'm actually using a Criteria query, this is what it looks like:

Criteria crit = session.createCriteria(CreativeCredit.class);
crit.add(Expression.eq("id", new Long(id)));
crit.setFetchMode("albums", FetchMode.EAGER);

return crit.uniqueResult();

If I'm doing this the wrong way, feel free to let me know.

Otherwise, I guess I'll just sort the albums in the code....