Hibernate Community • View topic - Problems with HQL and Collections

View unanswered posts | View active topics

Board index » Hibernate & Java Persistence » Hibernate Users

All times are UTC - 5 hours [ DST ]

Problems with HQL and Collections

Page 1 of 1

[ 7 posts ]

Previous topic | Next topic

Author

Message

sigrist

Post subject: Problems with HQL and Collections

Posted: Sun Dec 04, 2005 10:41 am

Newbie

Joined: Fri Aug 05, 2005 7:26 am
Posts: 17
Location: Santa Barbara d´Oeste SP Brasil

I have a problem trying to create a HQL using collection.

I have this tables:

Code:

ac_company
----------
id: integer
name: varchar(50)

ac_group
---------
id: integer
name: varchar(50)

ac_user
-------
id: integer
name: varchar(50)

ac_comp_group
-------------
comp_id: integer
group_id: integer

ac_user_group
-------------
user_id: integer
group_id: integer

I have the respectives classes Company, Group and User. The mapping of the collections is here:

Company:

Code:

       <set
            name="allowedGroups"
            table="ac_comp_group"
            lazy="true"
            cascade="none"
            sort="unsorted"
        >

            <key
                column="comp_id"
            >
            </key>

            <many-to-many
                class="com.mmi.access.Group"
                column="group_id"
                outer-join="true"
             />

        </set>

User:

Code:

        <set
            name="groups"
            table="ac_Group_User"
            lazy="false"
            cascade="none"
            sort="unsorted"
        >

            <key
                column="User_Id"
            >
            </key>

            <many-to-many
                class="com.mmi.access.Group"
                column="Group_Id"
                outer-join="auto"
             />

        </set>

I'm trying to get all user's companies. The HQL that I tried was:

from Company c where c.allowedGroups in (:groups)

and to execute the query, I did:

Code:

Query q = session.createQuery(query);

q.setParameterList("groups", user.getGroups);

List result = q.list();

The query that hibernate make is:

Code:

select company0_.id as id, 
       company0_.name as name25_, 
       company0_.abbr as abbr25_, 
       company0_.description as descript4_25_, 
       company0_.active as active25_ 
  from ac_company company0_, 
       ac_comp_group allowedgro1_, 
       ac_group group2_ 
 where company0_.id=allowedgro1_.comp_id 
   and allowedgro1_.group_id=group2_.id 
   and (. in (? , ? , ? , ?)) // THE ERROR IS HERE
   

I don't know why, hibernate doesn't put the name of the column in the last AND.
I tryed this query in the SQL Server, replacing the single "." by "group2_.id" and the query works fine:

Code:

select company0_.id as id, 
       company0_.name as name25_, 
       company0_.abbr as abbr25_, 
       company0_.description as descript4_25_, 
       company0_.active as active25_ 
  from ac_company company0_, 
       ac_comp_group allowedgro1_, 
       ac_group group2_ 
 where company0_.id=allowedgro1_.comp_id 
   and allowedgro1_.group_id=group2_.id 
   and (group2_.id in (1 , 2 , 3 , 4))

What's is wrong?

I´m using Java 1.4, Hibernate 3.0.5, MSSqlserver

Thanks in advance!
Sigrist

Top

sigrist

Post subject:

Posted: Thu Dec 08, 2005 6:12 am

Newbie

Joined: Fri Aug 05, 2005 7:26 am
Posts: 17
Location: Santa Barbara d´Oeste SP Brasil

Nobody can help-me?

Top

gavin

Post subject:

Posted: Thu Dec 08, 2005 6:18 am

Hibernate Team

Joined: Tue Aug 26, 2003 12:50 pm
Posts: 5130
Location: Melbourne, Australia

Well, your query is not valid HQL. So start there.

Top

sigrist

Post subject:

Posted: Thu Dec 08, 2005 7:08 am

Newbie

Joined: Fri Aug 05, 2005 7:26 am
Posts: 17
Location: Santa Barbara d´Oeste SP Brasil

Well, at least a reply! ;)

I thought that query was valid, although very simple, but isn't.

How can you make this query? I have an user with a set of groups and a Company with a set of groups. I want to get all companies that has the same groups that the user. Should I have inner joins?

Thanks!

Top

sigrist

Post subject:

Posted: Thu Dec 08, 2005 9:55 am

Newbie

Joined: Fri Aug 05, 2005 7:26 am
Posts: 17
Location: Santa Barbara d´Oeste SP Brasil

sigrist wrote:

Should I have to use inner joins?

Top

FMcGeough

Post subject:

Posted: Thu Dec 08, 2005 11:12 am

Newbie

Joined: Tue Oct 25, 2005 12:32 pm
Posts: 13

Your HQL is c.allowedGroups in etc.etc.....

c.allowedGroups is your object. You need an element of that object to do an in subquery.

What column in your database are you wanting to do the in with? Pass that in with HQL. i.e. c.allowedGroups.comp_id (or whatever) and then pass in an array of that column type.

Hibernate is not going to be able to decide for you what column you want to do your in subquery on.

Top

sigrist

Post subject:

Posted: Sun Dec 11, 2005 9:24 am

Newbie

Joined: Fri Aug 05, 2005 7:26 am
Posts: 17
Location: Santa Barbara d´Oeste SP Brasil

Hey man!

Thanks a lot!!! Your tip was right!

I did the query

Code:

select distinct c from Company c where c.allowedGroups.id in (:groups)

and now works!!!

Thanks a lot!!

Top

Page 1 of 1

[ 7 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