Hibernate Books

All times are UTC - 5 hours [ DST ]



Post new topic Reply to topic  [ 2 posts ] 
Author Message
 Post subject: HQL distinct + order by collection size
PostPosted: Mon May 21, 2012 10:50 am 
Newbie

Joined: Thu Mar 12, 2009 2:49 pm
Posts: 9
Location: Vienna
Hi there! I've got the following problem.

I would have liked to run this HQL:

Code:
SELECT distinct obj from at.ac.fhcampuswien.atom.shared.domain.PortalPerson obj
  LEFT OUTER JOIN obj.stellen stn
  LEFT OUTER JOIN stn.stelle str
  LEFT OUTER JOIN str.orgeinheit oe
  WHERE (1 = 1) AND (Per_ID = 15237 OR oe.OrE_ID IN (-1))
  ORDER BY obj.stellen.size ASC


Since that gave me the error message that all order by clauses have to be selected if distinct is used, I adapted the HQL-query to:
Code:
SELECT distinct obj from at.ac.fhcampuswien.atom.shared.domain.PortalPerson obj
  , obj.stellen.size
  LEFT OUTER JOIN obj.stellen stn
  LEFT OUTER JOIN stn.stelle str
  LEFT OUTER JOIN str.orgeinheit oe
  WHERE (1 = 1) AND (Per_ID = 15237 OR oe.OrE_ID IN (-1))
  ORDER BY obj.stellen.size ASC


However, this still gives me the same error message, since this is translated to:

Code:
    select
        distinct top 22 portalpers0_.objectID as col_0_0_,
        (select
            count(stellen4_.person_ObjectID)
        from
            Campus_Daten.dbo.ATOM_Org_Stellen_Personen stellen4_
        inner join
            DomainObject stellen4_1_
                on stellen4_.objectID=stellen4_1_.objectID
        where
            portalpers0_.objectID=stellen4_.person_ObjectID) as col_1_0_,
        portalpers0_.objectID as objectID0_,
        portalpers0_1_.stringRepresentation as stringRe2_0_,
        portalpers0_.CIS_Node as CIS1_16_,
        portalpers0_.Per_GeburtsDat as Per2_16_,
        portalpers0_.Per_Geschlecht as Per3_16_,
        portalpers0_.Per_ID as Per4_16_,
        portalpers0_.Per_Nachname as Per5_16_,
        portalpers0_.Per_SVNr as Per6_16_,
        portalpers0_.Per_Titel as Per7_16_,
        portalpers0_.Per_Vorname as Per8_16_
    from
        Campus_Daten.dbo.ATOM_Personen portalpers0_
    inner join
        DomainObject portalpers0_1_
            on portalpers0_.objectID=portalpers0_1_.objectID
    left outer join
        Campus_Daten.dbo.ATOM_Org_Stellen_Personen stellen1_
            on portalpers0_.objectID=stellen1_.person_ObjectID
    left outer join
        DomainObject stellen1_1_
            on stellen1_.objectID=stellen1_1_.objectID
    left outer join
        Campus_Daten.dbo.ATOM_Org_Stellen portalorgs2_
            on stellen1_.orgStelle_ObjectID=portalorgs2_.objectID
    left outer join
        DomainObject portalorgs2_1_
            on portalorgs2_.objectID=portalorgs2_1_.objectID
    left outer join
        Campus_Daten.dbo.ATOM_Orgeinheiten portalorge3_
            on portalorgs2_.orgEinheitObjectID=portalorge3_.objectID
    left outer join
        DomainObject portalorge3_1_
            on portalorge3_.objectID=portalorge3_1_.objectID
    where
        1=1
        and (
            Per_ID=15237
            or portalorge3_.OrE_ID in (
                -1
            )
        )
    order by
        (select
            count(stellen5_.person_ObjectID)
        from
            Campus_Daten.dbo.ATOM_Org_Stellen_Personen stellen5_
        inner join
            DomainObject stellen5_1_
                on stellen5_.objectID=stellen5_1_.objectID
        where
            portalpers0_.objectID=stellen5_.person_ObjectID) ASC


Can I somehow tell hibernate that it needs to translate the obj.stellen.size into the same SQL code both times?


Top
 Profile  
 
 Post subject: Re: HQL distinct + order by collection size
PostPosted: Mon May 21, 2012 11:02 am 
Newbie

Joined: Thu Mar 12, 2009 2:49 pm
Posts: 9
Location: Vienna
okey, wasn't actually that hard ;) here's the answer:

Code:
SELECT distinct obj from at.ac.fhcampuswien.atom.shared.domain.PortalPerson obj
  , obj.stellen.size as distorderby
  LEFT OUTER JOIN obj.stellen stn
  LEFT OUTER JOIN stn.stelle str
  LEFT OUTER JOIN str.orgeinheit oe
  WHERE (1 = 1) AND (Per_ID = 15237 OR oe.OrE_ID IN (-1))
  ORDER BY distorderby ASC


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 2 posts ] 

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.