many-to-many bidirectional relationship query

[russell stoneyroyd]

Hi
I want to do a bidirectional many-to-many query with HQL but the middle table is a link table. The problem is this is not an object so hibernate is not recognising it and sending back "QueryException in expected: rm" even though it is being used in the query. The mapping is:
<bag name="users" lazy="true" inverse="true" table="RoleMembership">
this has a many-to-many relationship inside it for the User class. The query when changed from objects to tables works perfectly. Are you able to use a link table in an hql query if it is not an object?
Cheers

[Paul Sturrock]

Are you able to use a link table in an hql query if it is not an object?

No. HQL works on objects only. If you've mapped the many-to-many corretly though, you shouldn't have to. e.g.:

<hibernate-mapping>
<class  name="com.Table1"
                table="table_1"
                ...
        >
        ...
 
       <set name="Table2"
             table="link_table"
              ...
         >
       <key column="table_1_pk" />
       <many-to-many column="table_2_pk" class="com.Table2" />
    </class>
</hibernate-mapping>

No I can join like this:

Table.Table2

[ September 13, 2006: Message edited by: Paul Sturrock ]

[russell stoneyroyd]

Thanks for your help.
Thing is all I really need to do is a "select count(*)" on the table. Which I'm acessing through a link table that does exist as a Collection but is the wrong object type.
The mapping for the role class users is:

 
<bag name="users" lazy="true" inverse="true" table="RoleMembership">
 <cache usage="nonstrict-read-write" />
  <key>
<column name="roleId" />
  </key>
  <many-to-many class="...User">
<column name="userId" />
  </many-to-many>
</bag>
   

Role membership exists in user but only ever has roles added to it.
From the Role object we want to access the users that have been deleted which is just a simple boolean flag on that user.
Tried using the 2 tables linked together and it didn't work properly.
Not really sure whether I can just use native sql for this one as I only want to return a count of the users.
Cheers

[Paul Sturrock]

Not really sure whether I can just use native sql for this one as I only want to return a count of the users

Ultimately you could, but in this case you don't really have to.

Tried using the 2 tables linked together and it didn't work properly.

How did it fail? Any exceptions?