HQL help

amshali · **Joined:** Sat Nov 06, 2004 11:19 am **Posts:** 4

Hi,

I've got three Hibernate entity; User, Report, and Role
Their relationship is as follows:

User <-----n, m-----> Role <-----n, m----- Report

User entity has a field called "userName" and I have the value of this field. I want to have the list of reports that is associated with this user. How can I get this list with a HQL query?

Thanks in advance

anthony · **Posted:** Sat Nov 06, 2004 11:38 am

what about showing simplified mapping files?

amshali · **Joined:** Sat Nov 06, 2004 11:19 am **Posts:** 4

Code:

<hibernate-mapping>
    <class name="Role" dynamic-update="false"
        dynamic-insert="false">
       <id
            name="title"
            column="title"
            type="java.lang.String"
            length="100"
        >
            <generator class="assigned">
            </generator>
        </id>

        <set
            name="users"
            table="User_Role"
            lazy="true"
            inverse="false"
            cascade="none"
            sort="unsorted">
              <key
                  column="title">
              </key>

              <many-to-many
                  class="User"
                  column="userName"
                  outer-join="auto"/>
        </set>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class
        name="User"
        dynamic-update="false"
        dynamic-insert="false">
        <id
            name="userName"
            column="userName"
            type="java.lang.String"
            length="100">
            <generator class="assigned">
            </generator>
        </id>
        <set
            name="roles"
            table="User_Role"
            lazy="true"
            inverse="false"
            cascade="all"
            sort="unsorted"
            order-by="userName">
              <key
                  column="userName">
              </key>
              <many-to-many
                  class="Role"
                  column="title"
                  outer-join="auto"/>
        </set>
    </class>
</hibernate-mapping>

<hibernate-mapping>
    <class
        name="Report"
        dynamic-update="false"
        dynamic-insert="false">
        <id
            name="reportId"
            column="reportId"
            type="java.lang.Integer">
            <generator class="identity">
            </generator>
        </id>
        <property
            name="description"
            type="java.lang.String"
            update="true"
            insert="true"
            access="property"
            column="description"
            not-null="true"
        />
        <set
            name="roles"
            table="Report_Role"
            lazy="true"
            inverse="false"
            cascade="all"
            sort="unsorted"
            order-by="fullyQualifiedName">
              <key
                  column="reportId"
              >
              </key>
              <many-to-many
                  class="Role"
                  column="title"
                  outer-join="auto"
               />
        </set>
    </class>
</hibernate-mapping>

[/code]

anthony · **Posted:** Sat Nov 06, 2004 11:58 am

ok we have

U * -- * Ro * -- * Re

select ro.reports
from Role ro, User usr
where
usr.userName = :userName
and usr in elements (ro.users)

amshali · **Joined:** Sat Nov 06, 2004 11:19 am **Posts:** 4

but Report is not naviagable from Role. I mean we don't have field reports in Role. So What shall I do?

anthony · **Posted:** Sat Nov 06, 2004 12:07 pm

you can find yourself, do it in two queries...

amshali · **Joined:** Sat Nov 06, 2004 11:19 am **Posts:** 4

Code:

[color=red]net.sf.hibernate.QueryException: unindexed collection before []: user1_.roles [select r from Report r, User u  where u.userName='b' and u.roles in elements(r.roles)][/color]

[/code]