hql many-to-many fetching

birju · **Posted:** Tue Aug 17, 2004 8:39 am

Hi,
I'm trying to figure out how to implement something in HQL

I have 3 tables:

Code:

PROJECT(ID, DESCRIPTION)
PROJECT_USER(PROJECT, USER)
USER(ID, USERNAME, FIRST_NAME, LAST_NAME)

Where the PROJECT_USER table is the relationship table for a many-to-many mapping as a set between PROJECT and USER:

In my project hbm file

Code:

    

        <set name="users" table="project_user">
          <key column="project">
            <column name="id" not-null="true"/>
          </key>
          <many-to-many class="net.bookingasp.data.core.user.pojo.User">
            <column name="user" not-null="true"/>
          </many-to-many>
        </set>

I want to get all PROJECTs (as pojos) associated with a particular USER.
In SQL, I'd do something like the following:

Code:

SELECT P.ID, P.DESCRIPTION
  FROM PROJECT P,
       PROJECT_USER PU,
       USER U
 WHERE P.ID = PU.PROJECT
   AND PU.USER = U.ID
   AND U.USERNAME = 'CLINTONB';

But how do i do this with hql??? I'm completely lost...
It must be simple, right?

Many thanks in advance,
Birju

birju · **Posted:** Tue Aug 17, 2004 2:39 pm

i've figured it out, cheers.
it was very simple indeed!

anthony · **Posted:** Tue Aug 17, 2004 5:35 pm

hi,

Quote:

i've figured it out, cheers.

give the solution of your pb, it might help other users who read your topic

birju · **Posted:** Wed Aug 18, 2004 4:55 am

Sure....

It was a 'join' with alias (bypassing the many-to-many table)

Code:

select p 
  from net.bookingasp.data.workflow.process.pojo.Project p
  join p.users u
 where u.login = :login

Birju