Please give a hint to convert this sql query into hql

Cnet · **Joined:** Fri Oct 15, 2004 5:37 am **Posts:** 1

Hibernate version: 2.1

Database: MySQL 4.0.17-nt

My Problem:
I want to get all projects from a specific user when he is the user of a project or when he is in the project team of a project:
The SQL Query works but i have problems to write the hql counterpart

select distinct p.*
from User as u, PROJECT_TEAM as t, PROJECT as p
where p.USER_ID = ? or (p.ID = t.PROJECT_ID and t.USER_ID = u.ID and u.id = ?)

Mapping documents:

########### PROJECT MAPPING #############
<hibernate-mapping>
<class name="com.cosinex.ikm.shared.model.basic.common.project.AbstractProject" table="PROJECT">

<id name="id" type="long" column="ID">
<column name="ID" length="10" not-null="true" unique="true" />
<generator class="net.sf.hibernate.id.IncrementGenerator" />
</id>

<property name="title" column="TITLE"/>
<property name="referenceNumber" column="REFERENCE_NUMBER"/>
<property name="projectStatus" column="PROJECT_STATUS"/>

<many-to-one name="user" class="com.cosinex.ikm.shared.model.basic.mandator.user.User" column="USER_ID" cascade="none"/>

<property name="projectType" column="PROJECT_TYPE"/>
<property name="mandatorId" column="MANDATOR_ID"/>
<property name="creationDate" column="CREATION_DATE"/>
<property name="publishingDate" column="PUBLISHING_DATE"/>

<property name="deleted" column="DELETED" not-null="true"/>

<set name="projectTeam" table="PROJECT_TEAM" lazy="true">
<key column="PROJECT_ID"/>
<many-to-many column="USER_ID" class="com.cosinex.ikm.shared.model.basic.mandator.user.User"/>
</set>

<joined-subclass name="com.cosinex.ikm.shared.model.basic.common.project.tender.Tender" table="TENDER">
<key column="PROJECT_ID"/> </joined-subclass>

<joined-subclass name="com.cosinex.ikm.shared.model.basic.common.project.auction.Auction" table="AUCTION">
<key column="PROJECT_ID"/>
</joined-subclass>

</class>
</hibernate-mapping>

########### USER MAPPING #############
<hibernate-mapping>

<class name="com.cosinex.ikm.shared.model.basic.mandator.user.User" table="USER">

<id name="id" type="long" column="ID" unsaved-value="null">
<column name="ID" length="10" not-null="true" unique="true" />
<generator class="net.sf.hibernate.id.IncrementGenerator" />
</id>

<property name="salutation" column="SALUTATION" not-null="false"/>
<property name="title" column="TITLE"/>
<property name="firstName" column="FIRST_NAME" not-null="true"/>
<property name="lastName" column="LAST_NAME" not-null="true" />
<property name="department" column="DEPARTMENT" />
<property name="phone" column="PHONE" />
<property name="fax" column="FAX" />
<property name="email" column="EMAIL" />
<property name="password" column="PASSWORD"/>
<property name="status" column="STATUS"/>
<property name="mandatorId" column="MANDATOR_ID" />

<set name="roles" table="USER_ROLE">
<key column="USER_ID"/>
<many-to-many column="ROLE_ID" class="com.cosinex.ikm.shared.model.basic.mandator.user.Role" />
</set>

<property name="allowedToPublish" column="ALLOWED_TO_PUBLISH" not-null="true"/>
<property name="deleted" column="DELETED"/>

</class>
</hibernate-mapping>

shl · **Posted:** Fri Oct 15, 2004 11:01 am

Try this

Code:

select distict project from Project project
   left join p.user user
   left join p.projectTeam teamPlayer
where
   user.id = 123
   or
   teamPlayer.id = 123