Hibernate Community • View topic

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Help with query

Page 1 of 1

[ 8 posts ]

Previous topic | Next topic

Author

Message

nvolpini

Post subject: Help with query

Posted: Tue Oct 28, 2003 2:58 pm

Regular

Joined: Tue Aug 26, 2003 3:34 pm
Posts: 54
Location: Farroupilha - Brasil

I need a help to implement a query ...

Code:

User * ----------> * Group

table user (id integer)
table group (id integer)
table user_group(user_id integer, group_id integer) 

class User {
   int id;
   String name;
   Set groups; //many-to-many
   ....
}

class Group {
   int id;
   String name;
   ...
}

I need to query the groups that are not associated with a specific User.

With SQL I use:

Code:

SELECT * FROM group AS g WHERE NOT EXISTS (SELECT * FROM user_group AS ug WHERE g.id = ug.group_id AND ug.user_id = X;

or

select * from group g inner join user_group ug on g.id<>ug.group_id where user_id = X; 

How can I get the same result using Hibernate Query Language ?

Thans, Neimar

Top

steve

Post subject:

Posted: Tue Oct 28, 2003 3:23 pm

Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX

Code:

select distinct elements(u.groups) from User as u where u.id <> :userId

gets you all groups associated with any users other then the one given by userId and is functional equivalent to your second sql query. Note that unassociated groups (in the sql also) are not returned.

select g from Group as g
where NOT EXISTS (
select u.id from User as u join u.groups as ug
where ug.id = g.id and u.id = userId
)

Top

cutie

Post subject:

Posted: Tue Oct 28, 2003 3:25 pm

Regular

Joined: Tue Sep 16, 2003 11:35 am
Posts: 93
Location: San Francisco, CA

Try

select group from Group as group
where :user not in elements(group.users)

http://hibernate.bluemars.net/hib_docs/ ... anguage-s6

Top

steve

Post subject:

Posted: Tue Oct 28, 2003 3:39 pm

Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX

That only works if the Group object has a collection reference to its User associations. The the model shows thats not the case.

Top

cutie

Post subject:

Posted: Tue Oct 28, 2003 3:48 pm

Regular

Joined: Tue Sep 16, 2003 11:35 am
Posts: 93
Location: San Francisco, CA

good point ...

Top

nvolpini

Post subject:

Posted: Tue Oct 28, 2003 4:14 pm

Regular

Joined: Tue Aug 26, 2003 3:34 pm
Posts: 54
Location: Farroupilha - Brasil

I need to show all the groups even the unassociated with any user ...

In this case I will change my model and use cutie's suggestion. It works fine and solve my problem.

If I don't want to change my model ... exists another alternative ?

Top

steve

Post subject:

Posted: Tue Oct 28, 2003 6:19 pm

Hibernate Team

Joined: Tue Aug 26, 2003 3:00 pm
Posts: 1816
Location: Austin, TX

Yeah, the second query I posted. Provided your db supports sub-selects, which I assume it does since one of your sql query was using sub-selects.

Top

nvolpini

Post subject:

Posted: Tue Oct 28, 2003 7:07 pm

Regular

Joined: Tue Aug 26, 2003 3:34 pm
Posts: 54
Location: Farroupilha - Brasil

I prefer to evict sub-queries ... to keep the code portable for other databases too.

Thanks a lot.

Top

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