-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

View unanswered posts | View active topics


Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]


Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 
  Previous topic | Next topic 
Author Message
bowa
 Post subject: sort-by on set with many-to-many problem
PostPosted: Wed Mar 22, 2006 5:55 am 
Newbie

Joined: Wed Mar 22, 2006 5:43 am
Posts: 15
Location: Belgium
i am using hibernate3

from my mapping file :

Code:
<set name="paragraphs"
   table="article_paragraph"
   cascade="all"
   order-by="weight asc"
   >
      <key column="articleid"/>
      <many-to-many
         column="paragraphid"
         class="my.package.Paragraph"
      />
</set>



weight is a property of a Paragraph bean.

the sql that gets generated:

Code:
select
paragraphs0_.articleid as articleid1_,
paragraphs0_.paragraphid as paragrap2_1_,
paragraph1_.id as id0_,
paragraph1_.paragraph as paragraph4_0_,
paragraph1_.type as type4_0_,
paragraph1_.weight as weight4_0_,
paragraph1_.binaryid as binaryid4_0_
from
article_paragraph paragraphs0_
inner join paragraphs paragraph1_ on paragraphs0_.paragraphid=paragraph1_.id where paragraphs0_.articleid=1
order by paragraphs0_.weight asc


it is trying to find the weight column on the article_paragraph table

while i would want it to get it on the paragraphs table like this: ( i changed the above sql to the code i want -- only changed the last line)

Code:
select
paragraphs0_.articleid as articleid1_,
paragraphs0_.paragraphid as paragrap2_1_,
paragraph1_.id as id0_,
paragraph1_.paragraph as paragraph4_0_,
paragraph1_.type as type4_0_,
paragraph1_.weight as weight4_0_,
paragraph1_.binaryid as binaryid4_0_
from
article_paragraph paragraphs0_
inner join paragraphs paragraph1_ on paragraphs0_.paragraphid=paragraph1_.id where paragraphs0_.articleid=1
order by paragraph1_.weight asc



am i wrong somewhere oris there really no way to do this with order-by ?

i know i can do it with "sort" and a comparator but i would like the db to do the sorting instead of having it done in memory.
Top
 Profile  
 
urs_b
 Post subject:
PostPosted: Wed Mar 22, 2006 6:45 am 
Regular
Regular

Joined: Tue Jan 03, 2006 9:52 am
Posts: 52
Location: Zurich
Hello,
I think that you cannot define in your mapping document.
But maybe you can try with the Criteria option:

List l = session.createCritera(Paragraph.class).addOrder(Order.asc(weight)).list();

Urs
Top
 Profile  
 
bowa
 Post subject:
PostPosted: Wed Mar 22, 2006 6:50 am 
Newbie

Joined: Wed Mar 22, 2006 5:43 am
Posts: 15
Location: Belgium
does this sorting happens in memory or on db ?

i have a working version with a ParagraphComparator but i just wanted let the db handle the sorting.
Top
 Profile  
 
urs_b
 Post subject:
PostPosted: Wed Mar 22, 2006 7:25 am 
Regular
Regular

Joined: Tue Jan 03, 2006 9:52 am
Posts: 52
Location: Zurich
Yes, it does it in the database, as you wanted.

Hope this helps!
Urs
Top
 Profile  
 
bowa
 Post subject:
PostPosted: Wed Mar 22, 2006 7:55 am 
Newbie

Joined: Wed Mar 22, 2006 5:43 am
Posts: 15
Location: Belgium
Ok, that would be nice but i dont really see how to use it.

As i am never retrieving the paragraphs themselves. they are a Set on my Article bean. (the mapping in my first post is fromt he Article.hbm.xml mapping file )

So in my DAO i use:


Code:
   public Article getArticle(Integer id) throws Exception {
      
      HibernateTemplate template = getHibernateTemplate();
      
      String hql =
         "from " + Article.class.getName() + " as article where article.id = :id";
      
      List results = template.findByNamedParam(hql, "id", id);
      
      if (results.size() == 0)
         throw new Exception("No Article with id " + id);
      
      return (Article) results.get(0);
   }



i can get the sesion like this :

Code:
Session session = SessionFactoryUtils.getSession(template.getSessionFactory(), false);
      session.createCriteria(Paragraph.class).addOrder(Order.asc("weight"));


but from what you told and what i found online this is used as a seperate query, returning a list of Paragraphs, but i want them to be set to my Article bean as is happening now automatically by querying for one or more Articles.
Top
 Profile  
 
urs_b
 Post subject:
PostPosted: Wed Mar 22, 2006 8:15 am 
Regular
Regular

Joined: Tue Jan 03, 2006 9:52 am
Posts: 52
Location: Zurich
Yes, you are right, that does not work.
Any other ideas... ?
Top
 Profile  
 
urs_b
 Post subject:
PostPosted: Wed Mar 22, 2006 9:19 am 
Regular
Regular

Joined: Tue Jan 03, 2006 9:52 am
Posts: 52
Location: Zurich
A bit nasty (and works as long as the naming strategy does not change between hibernate versions):

<set name="paragraphs" table="article_paragraph" cascade="all"
order-by="paragraph1_.weight asc" >
Top
 Profile  
 
bowa
 Post subject:
PostPosted: Wed Mar 22, 2006 9:41 am 
Newbie

Joined: Wed Mar 22, 2006 5:43 am
Posts: 15
Location: Belgium
i had tried that but it then uses


Code:
order by paragraphs0_.paragraph1_.weight asc


so that fails too
Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  Page 1 of 1
  [ 8 posts ] 

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.