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.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 posts ] 
Author Message
 Post subject: rather complex HQL query
PostPosted: Tue Jun 12, 2007 8:53 am 
Newbie

Joined: Wed Mar 22, 2006 5:43 am
Posts: 15
Location: Belgium
Hibernate version: 3.2.3
Mapping documents:
Project:
Code:
<hibernate-mapping>
   <class name="my.Project" table="project">
      <id name="id" column="id">
         <generator class="native"/>
      </id>
      ...
      <set name="userACLs" inverse="true">
         <key column="project"/>
         <one-to-many class="my.UserACLRecord"/>
      </set>
   </class>
</hibernate-mapping>


Code:
<hibernate-mapping>
       <class name="my.UserACLRecord" table="userACLRecord">
          <id name="id" column="id">
             <generator class="native"/>
          </id>
          <many-to-one name="user" class="my.User" not-null="true"/>
          <many-to-one name="project" class="my.Project" not-null="true"/>
          <property name="read"/>
          <property name="write"/>
          <property name="execute"/>
       </class>
</hibernate-mapping>


Ok, so i have a Project that has a set of UserACLRecords, and each of these UserACLRecords is linked to a User object.

Now i would like to get retrieve all Projects that have a UserACLRecord for User X that has the 'read' property set to true (1 in db).

What is the cleanest way to do this in HQL ? i guess it will have to be something with 'in elements' but i am not really getting there.

thanks in advance for some help.


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 12, 2007 10:44 am 
Newbie

Joined: Wed Mar 22, 2006 5:43 am
Posts: 15
Location: Belgium
this does the trick ...

Code:
return getHibernateTemplate().find("select p from Project p, UserACLRecord uaclr where p = uaclr.project and uaclr.read = true and uaclr.user = ?", user);


but it looks dirty too me ... especially because its just part of my problem

next to UserACLRecords there are also UserGroupACLRecords

so i also want to retrieve all Projects that where there is a UserGroupACLRecord that has read permission for any UserGroup a User belongs to.

so
Code:
there is a User:

<hibernate-mapping>
   <class name="my.User" table="user">
        <id name="id" column="id">
            <generator class="native"/>
        </id>
        ...
        <set name="userGroups" table="user_userGroup" cascade="save-update">
           <key column="user"/>
           <many-to-many column="userGroup" class="my.UserGroup"/>
        </set>
   </class>
</hibernate-mapping>


and i found some HQL that manages to to just that ... (again it looks quite dirty to me)
Code:
return getHibernateTemplate().find("select p " +
   "from Project p, UserACLRecord uaclr, UserGroupACLRecord ugaclr, User user " +
   "where p = ugaclr.project and ugaclr.read = true and ugaclr.userGroup in elements(user.userGroups) and user=?", newObject[] {user});


but now i want to combine both of them ...

Code:
return getHibernateTemplate().find("select p " +
      "from Project p, UserACLRecord uaclr, UserGroupACLRecord ugaclr, User user " +
      "where (p = ugaclr.project and ugaclr.read = true and ugaclr.userGroup in elements(user.userGroups) and user=?) " +
      "or (p = uaclr.project and uaclr.read = true and uaclr.user = ?)", new Object[] {user, user});


this doesnt give any errors and it only returns projects that match the criteria ... but there are duplicates ... (i get the project that matches the UserACLRecord 3 times ...)

is there a cleaner way to do this and avoid the duplicates ?


Top
 Profile  
 
 Post subject: select distinct
PostPosted: Tue Jun 12, 2007 11:40 am 
Newbie

Joined: Wed Apr 26, 2006 10:06 am
Posts: 7
Quote:
Code:
return getHibernateTemplate().find("select p " +
      "from Project p, UserACLRecord uaclr, UserGroupACLRecord ugaclr, User user " +
      "where (p = ugaclr.project and ugaclr.read = true and ugaclr.userGroup in elements(user.userGroups) and user=?) " +
      "or (p = uaclr.project and uaclr.read = true and uaclr.user = ?)", new Object[] {user, user});


this doesnt give any errors and it only returns projects that match the criteria ... but there are duplicates ... (i get the project that matches the UserACLRecord 3 times ...)

is there a cleaner way to do this and avoid the duplicates ?



Probably this will work:
return getHibernateTemplate().find("select distinct p " +
"from Project p, UserACLRecord uaclr, UserGroupACLRecord ugaclr, User user " + ...


Top
 Profile  
 
 Post subject:
PostPosted: Tue Jun 12, 2007 11:49 am 
Newbie

Joined: Wed Mar 22, 2006 5:43 am
Posts: 15
Location: Belgium
thanks !!! that did work ...

but doesnt it look really dirty such a big query ? now it goes fast but no idea how it going to respond when the db grows bigger.


Top
 Profile  
 
 Post subject:
PostPosted: Wed Jun 13, 2007 12:53 am 
Newbie

Joined: Wed Apr 26, 2006 10:06 am
Posts: 7
bowa wrote:
thanks !!! that did work ...

but doesnt it look really dirty such a big query ? now it goes fast but no idea how it going to respond when the db grows bigger.


It looks dirty - i don't know how to optimize it :). Fill db with data for test...


Top
 Profile  
 
 Post subject:
PostPosted: Thu Nov 15, 2007 10:08 am 
Newbie

Joined: Thu May 12, 2005 10:40 am
Posts: 16
I had a similar problem. elements is weak documented.
Doing a lot of trys finally it leeded me to something
which would be approximately the following for your query

Code:
from Project p
WHERE exists (select pACL.read FROM elements(p.userACLs) pACL WHERE  pACL.user = ? AND pACL.read=true)


which doesn't need a inner join and seems clearer to me...
(does a subquery! db must support)

i'm not sure whether
select pACL.read or pACL.user should be readed.
as far as i know 'exists' from sql, there's only need for one column...

*g
Helmut


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 6 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.