Heya,

A Session has a list (many-to-many) of Publications.
Given a Session, I want to get a subset of its Publications.

However, the Publications are never sorted.
I can see in the generated SQL that no "order by" clause is generated.

Am i wrong to assume Hibernate should sort it ?
Is there a way to use the index defined in the mapping, to force an order by (since this is not an attribute of any object, i don't figure how i could do that) ?

Any help appreciated, as i've already lost hours wondering how i could do what seemed to be a very simple query.


Hibernate: 2.0.3
Database: Postgresql 7.3.x


Here's the HSQL and SQL traces in the logfile
HQL:
select p
from org.nextime.ion.framework.business.impl.SectionImpl s
join s.publications p
join p.profils pro
where s.id = 70352897 and pro.id in (69304321 ) and (p.dateDebValidite <= current_date or p.dateDebValidite is null) and (p.dateFinValidite >= current_date or p.dateFinValidite is null)

08/03/04 15:38:33,209 [DEBUG] hql.QueryTranslator - SQL:
select publicat2_.id as id, publicat2_.name as name, publicat2_.workflow_type as workflow3_, publicat2_.datepubli as datepubli, publicat2_.datedebvalid as datedebv5_, publicat2_.datefinvalid as datefinv6_, publicat2_.metadata as metadata, publicat2_.type_id as type_id, publicat2_.version_online_id as version_9_, publicat2_.last_version_id as last_ve10_
from sections sectioni0_
inner join sections_publications publicat1_ on sectioni0_.id=publicat1_.section_id
inner join publications publicat2_ on publicat1_.publication_id=publicat2_.id
inner join publication_profil profils3_ on publicat2_.id=profils3_.publication_id
inner join profil profilim4_ on profils3_.profil_id=profilim4_.id
where (sectioni0_.id=70352897 )and(profilim4_.id in(69304321))and((publicat2_.datedebvalid<=current_date )or(publicat2_.datedebvalid is null ))and((publicat2_.datefinvalid>=current_date )or(publicat2_.datefinvalid is null ))

Here's the java code of the query
StringBuffer query = new StringBuffer("select p from SectionImpl s ");
query.append(" join s.publications p ");
query.append(" join p.profils pro");
query.append(" where s.id = ").append(id);
query.append(" and pro.id in (");
Iterator it= user.getProfils().iterator();
ProfilImpl pro =null;
while(it.hasNext()){
pro = (ProfilImpl) it.next();
query.append(pro.getId());
if(it.hasNext()){
query.append(",");
}
}
query.append(" ) ");
query.append(" and (p.dateDebValidite <= current_date or p.dateDebValidite is null) ");
query.append(" and (p.dateFinValidite >= current_date or p.dateFinValidite is null)");

aList = dBService.getObjects(sess, query.toString());

Here's the mapping of both classes
<!-- SECTION -->
<class name="org.nextime.ion.framework.business.impl.SectionImpl"
table="sections" dynamic-update="false">
<id name="id" column="id" type="long" unsaved-value="null">
<generator class="net.sf.hibernate.id.TableHiLoGenerator">
<param name="table">HIBERNATE_UNIQUE_KEY</param>
<param name="column">NEXT_HI</param>
</generator>
</id>
<property name="name" column="name" type="string"/>
<property name="index" column="numindex" type="integer"/>
<property name="metaDataBytes" column="metadata" type="binary"/>

<many-to-one name="parent" column="parent_id"
class="org.nextime.ion.framework.business.impl.SectionImpl"/>

<list name="publications" table="sections_publications" lazy="true" >
<key column="section_id"/>
<index column="numindex"/>
<many-to-many
class="org.nextime.ion.framework.business.impl.PublicationImpl"
column="publication_id"/>
</list>
<set name="profils" table="section_profil" lazy="false">
<key column="section_id"/>
<many-to-many class="org.nextime.ion.framework.business.impl.ProfilImpl"
column="profil_id"/>
</set>
</class>

<!-- publication -->
<class name="org.nextime.ion.framework.business.impl.PublicationImpl"
table="publications" dynamic-update="false">
<id name="id" column="id" type="long" unsaved-value="null">
<generator class="net.sf.hibernate.id.TableHiLoGenerator">
<param name="table">HIBERNATE_UNIQUE_KEY</param>
<param name="column">NEXT_HI</param>
</generator>
</id>
<property name="name" column="name" type="string"/>
<!--a verifier <property name="index" column="numindex"
type="integer"/>-->
<property name="workflowType" column="workflow_type" type="string"/>
<property name="datePublication" column="datepubli" type="timestamp"/>
<property name="dateDebValidite" column="datedebvalid" type="timestamp"/>
<property name="dateFinValidite" column="datefinvalid" type="timestamp"/>
<property name="metaDataBytes" column="metadata" type="binary"/>

<many-to-one name="type" column="type_id"
class="org.nextime.ion.framework.business.impl.TypePublicationImpl"/>
<many-to-one name="versionOnLine" column="version_online_id"
class="org.nextime.ion.framework.business.impl.PublicationVersionImpl"/>
<many-to-one name="lastVersion" column="last_version_id"
class="org.nextime.ion.framework.business.impl.PublicationVersionImpl"/>
<set name="categories" table="categories_publications" lazy="false">
<key column="publication_id"/>
<many-to-many
class="org.nextime.ion.framework.business.impl.CategoryImpl"
column="category_id"/>
</set>

<set name="sections" table="sections_publications" lazy="false"
inverse="true">
<key column="publication_id"/>
<many-to-many
class="org.nextime.ion.framework.business.impl.SectionImpl"
column="section_id"/>
</set>
<set name="profils" table="publication_profil" lazy="false">
<key column="publication_id"/>
<many-to-many class="org.nextime.ion.framework.business.impl.ProfilImpl"
column="profil_id"/>
</set>

</class>

You are. Why Hibernate should order that stuff. It'll be lost by the list semantic anyway

The index column contains exactly the position of the item in the List. Hibernate will insert it just at list.add(index), so no need for a order by.

Well I do use a list to order that stuff.
So I'd expect Hibernate to keep the list order when retrieving objects.

I know we can do things like p[0] in the query, and if Hibernate don't keep the list order in that case ... then I'll consider it a bug.



Well, what do you mean exactly ?

Ok but the problem is, when using a query in HQL I cant retrieve the items in the same order as they are in the list. They are just unordered, as if I was using a Set.


I'll try to explain What I want to do a bit more.

I have a Section, that lists some Publications. Some Publications are online, some not.

So the section S1 lists 4 publications.
S1 { P1,P2,P3,P4 }

If i want all the publications that are online, in that case P1 and P3, I want a query that returns {P1, P3} and not {P3,P1}: the list order must be kept. I dont think it's too much to ask.

The thing is Hibernate does not do that by itself, and there seems to be no way to force it in HQL.[/img]

Doesn't adding "order by index(p)" to the hql solve that?

Well in SQL it is translated as "order by index(publicat2_.id)"
and postgreSql returns an error (the function index(bigint) is unknown)

So index() does not seems to be an HQL keyword.

But really I was thinking about something like that, maybe rather index(s.publications) (considering that in the Hibernate Mapping the index is declared with the list).

Didn't saw anything like that in the documentation, though.

:(

logs:
Hibernate: select publicat2_.id as id, publicat2_.name as name,
publicat2_.workflow_type as workflow3_, publicat2_.datepubli as datepubli,
publicat2_.datedebvalid as datedebv5_, publicat2_.datefinvalid as
datefinv6_, publicat2_.metadata as metadata, publicat2_.type_id as type_id,
publicat2_.version_online_id as version_9_, publicat2_.last_version_id as
last_ve10_
from sections sectioni0_ inner join sections_publications publicat1_ on
sectioni0_.id=publicat1_.section_id inner
join publications publicat2_ on publicat1_.publication_id=publicat2_.id
inner join publication_profil profils3_ on
publicat2_.id=profils3_.publication_id
innerjoin profil profilim4_ on profils3_.profil_id=profilim4_.id
where (sectioni0_.id=70352897 )and(profilim4_.id in(69304321))
and((publicat2_.datedebvalid<=current_date )or(publicat2_.datedebvalid is
null ))
and((publicat2_.datefinvalid>=current_date )or(publicat2_.datefinvalid is
null )) order by index(publicat2_.id)
org.nextime.ion.framework.exception.DataBaseException:
net.sf.hibernate.JDBCExce
ption: Could not execute query: ERROR: Function index(bigint) does not
exist
Unable to identify a function that satisfies the given argument
types
You may need to add explicit typecasts

at
org.nextime.ion.framework.persistentServices.DBService.getObjects(DBS
ervice.java:145)

Man you really should read the java.util.List javadoc. There is no mention on any particular order, only index.

You could try filtering your publications list:


I don't know if this will keep the list ordering either, though.

You might have to back all the way out to raw SQL for this:

It's painful, but it looks to me like it (or something like it) would do what you want.

It's an interesting gap you're highlighting in HQL; there's no way to filter an ordered collection in a way that preserves the order, or to implement a query that orders by the collection ordering of some association involved in the query.

Cheers!
Rob

Thanks a lot RobJellinghaus for your answer.

I'll try both of your solutions.

I'm not sure the filter will keep the order, either. It's the most elegant one, although the full collection is loaded, which could be a performance problem for me.

The raw SQL looks like it can do it. For sure it's painful, but I see it as a strength of Hibernate: what it can't do, you still can do it the old way.



That was the reason of my post, and I'm glad you see it that way, too.
I think such a feature would be of real interest in the future.

Thanks to you for your interest, and your efforts in helping me.

OK when I try that, Hibernate throws an Exception.
This occurs only if i keep the part with "this.profils.id in (78970881,69304321 )", it seems that the filter query cant make the necessary join on the profils collection.

I tried with the following syntax "this.profils in (78970881,69304321 )", the same error occured.


16/03/04 18:02:12,891 [ERROR] hibernate.AssertionFailure - An AssertionFailure occured - this may indicate a bug in Hibernate
net.sf.hibernate.AssertionFailure: bug in query parser: where (this.dateDebValidite <= current_date or this.dateDebValidite is null) and (this.dateFinValidite >= current_date or this.dateFinValidite is null) and this.profils.id in (78970881,69304321 )
at net.sf.hibernate.hql.QueryTranslator.addJoin(QueryTranslator.java:387)
at net.sf.hibernate.hql.QueryTranslator.addFrom(QueryTranslator.java:337)
at net.sf.hibernate.hql.QueryTranslator.addFrom(QueryTranslator.java:332)
at net.sf.hibernate.hql.QueryTranslator.addFromCollection(QueryTranslator.java:757)
at net.sf.hibernate.hql.FilterTranslator.compile(FilterTranslator.java:24)
at net.sf.hibernate.impl.SessionFactoryImpl.getFilter(SessionFactoryImpl.java:367)
at net.sf.hibernate.impl.SessionImpl.getFilterTranslator(SessionImpl.java:3052)
at net.sf.hibernate.impl.SessionImpl.filter(SessionImpl.java:3072)
at net.sf.hibernate.impl.SessionImpl.filter(SessionImpl.java:3008)
at org.nextime.ion.framework.business.SectionMgr.getPublicationsOfSection(SectionMgr.java:426)

Well, as expected, the filter does not mind the order of the list.
What's funny, it keeps using the table join that would be needed to retrieve the index ... too bad


16/03/04 18:56:07,101 [DEBUG] hql.QueryTranslator - HQL: where (this.dateDebValidite <= current_date or this.dateDebValidite is null) and (this.dateFinValidite >= current_date or this.dateFinValidite is null)
16/03/04 18:56:07,101 [DEBUG] hql.QueryTranslator - SQL: select this.id as id, this.name as name, this.workflow_type as workflow3_, this.datepubli as datepubli, this.datedebvalid as datedebv5_, this.datefinvalid as datefinv6_, this.metadata as metadata, this.type_id as type_id, this.version_online_id as version_9_, this.last_version_id as last_ve10_ from sections_publications publicat0_ inner join publications this on publicat0_.publication_id=this.id where publicat0_.section_id = ? and (((this.datedebvalid<=current_date )or(this.datedebvalid is null ))and((this.datefinvalid>=current_date )or(this.datefinvalid is null )))