Quick HQL query regarding elements

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

Quick question about querying on Set's. I have two classes, User and Group. User has a set of groups in a many-to-many relationship. I would like to select a List of users that contain a particular group. How would I go about this?

I've tried this:

Code:

from User user, user.groups as g where g.id=?

However that joins both users and groups.. all I really want is a list of users that contain that specific group, specifying the group's id. Is this possible?

I also tried

Code:

from User user where elements(user.groups).id=?

That threw an error =(

Help appreciated greatly!
-David

gavin · **Posted:** Thu Apr 01, 2004 8:03 pm

Code:

from User user where ? in elements(user.groups)

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

Thanks Gav =)

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

I went and tested that and it actually failed... I also tried id=? instead of the ?

Here's the error (Different classes, same type of setup)

Caused by: java.sql.SQLException: Syntax error or access violation, message from server: "You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select roles1_.role_id from adminGroup_Role roles1_ where group"

Code used:

Code:

Group g where ? in elements(g.roles)

The sql it generated looks like:

Code:

Hibernate: select group0_.id as id, group0_.name as name, group0_.description as descript3_ from adminGroup group0_ where (id=? in(select roles1_.role_id from adminGroup_Role roles1_ where group0_.id=roles1_.group_id))

Or without the id=

Hibernate: select group0_.id as id, group0_.name as name, group0_.description as descript3_ from adminGroup group0_ where (? in(select roles1_.role_id from adminGroup_Role roles1_ where group0_.id=roles1_.group_id))

in this case I have a Group and the group has a set of Role objects, trying to get all the groups containing a specific role object. Any more ideas?
thanks in advance,
David

emmanuel · **Posted:** Fri Apr 02, 2004 1:28 pm

You have to set a Group object as your parameter. and keep Gavin's sample

cpederson · **Joined:** Mon Apr 05, 2004 9:43 am **Posts:** 4

I have a similar situation, however, (using this example) I want to gather the users who are not part of a group.

I've tried numerous things but I just haven't found the right one yet.

Thanks,
Curt

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

Curt did you ever solve that? I would like to do the same thing... so I would want to select all the File's that are members of NO FileType. Not sure how I would go about that either.

Any ideas? :)

Thanks,
David

cpederson · **Joined:** Mon Apr 05, 2004 9:43 am **Posts:** 4

David,

Yep, I did solve it, but I ended up using standard SQL. In this instance, I'm selecting all parts that are not associated with an assembly. From the assembly object, you can call getParts() to retrieve the association records. I never did figure out how to get at assemblies from the Part.

Anyway, here is the query I used.

Code:

<sql-query name="net.my.package.om.part.loadavailableforassembly">
  <return alias="part" class="net.my.package.om.Part"/>
    select {part.*}
       from part left outer join assembly_part
               on part.id = assembly_part.part_id
               and assembly_part.assembly_id = :assembly_id
       where assembly_part.part_id is null
</sql-query>

This query is in my Part.hbm.xml file. The Assembly.hbm.xml file contains a <set> which defines the AssemblyPart composite-element. The composite-element contains some extra fields.

I hope this helps,
Curt

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

I was still never able to get the original example to work.. by binding a group to the parameter. Here's the resulting error and the SQL generated.

Code:

Caused by: java.sql.SQLException: Syntax error or access violation,  message from server: "You have an error in your SQL syntax.  Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select groups1_.group_id from adminUser_Group groups1_ where us"
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:1651)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:889)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:956)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:1874)
   at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1538)
   at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:205)
   at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:83)
   at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:790)
   at net.sf.hibernate.loader.Loader.doQuery(Loader.java:184)
   at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:128)
   at net.sf.hibernate.loader.Loader.list(Loader.java:918)
   at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:983)
   at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1473)
   ... 50 more


Hibernate: select user0_.id as id, user0_.administrator as administ2_, user0_.userName as userName, user0_.passwordHash as password4_, user0_.customerId as customerId, user0_.enabled as enabled, user0_.expirationDate as expirati7_, user0_.firstName as firstName, user0_.forumAccount as forumAcc9_, user0_.lastName as lastName, user0_.requiresAdministrator as require11_, user0_.email as email, user0_.fax as fax, user0_.phone as phone, user0_.userType_id as userTyp15_ from adminUser user0_ where (? in(select groups1_.group_id from adminUser_Group groups1_ where user0_.id=groups1_.user_id))

Any more ideas?
Thanks
-David[/code]

anthony · **Posted:** Fri Apr 23, 2004 3:34 pm

are you using session.createQuery and query.setParameter?

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

Yes here is the exact code I am using, note the group object is a fully loaded object from hibernate.

Code:

Query query = session.createQuery("from cmcflex.salesweb.model.admin.User user where ? in elements(user.groups)");
query.setParameter(0, group);
List usersWithGroup = query.list();

-David

anthony · **Posted:** Fri Apr 23, 2004 3:42 pm

try with named parameter instead

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

It looks to me like the code being generated is wrong somehow because I cut and paste that into SQLYog to test it.. and it didnt work, something near the subquery doesnt work right.

Any ideas?
David

halcyon · **Joined:** Wed Dec 17, 2003 1:58 pm **Posts:** 102

I tried that as well, using :GROUP as my parameter then calling query.setParameter("GROUP", group); Same problem. Its the generated sql thats wrong...

gavin · **Posted:** Fri Apr 23, 2004 10:33 pm

How about you share the generated SQL with us???

Are you sure your db supports subselects?