Quick newbie query problem here,

One Project has a set of tasks.
One Project has one manager (of type User)
One Task has one requestedBy (of type User)
One Task has one asssignedBy (of type User)

(see mapping files below if you like)

I want to create a query that will return all Projects that a user is involved with. Their User object could be referenced in any of the User fields above. What might this look like? I have the first part, and it looks something like this:

return getHibernateTemplate().find("select project from Project as project, User as user where user.id = " + user.getId() + " and project.manager = user");

This will return all the projects where a user is manager. Hrm.. and now that I think of it, there must be a way to send in the User object, rather then having to refer to his/her ID, isn't there? How???

And how am I going to write a query that returns all the projects where the user is a manager OR is mentioned in one of the Task User fields?

I've tried writing join and subqueries, but I'm stumped.

Thanks in advance!..

-Gregg

Mapping documents:

Project.hbm.xml
<hibernate-mapping>
<class name="org.appfuse.model.Project" table="project">
<id name="id" column="id" unsaved-value="null">
<generator class="increment"/>
</id>
<many-to-one name="manager" column="manager_id" class="org.appfuse.model.User"/>
<set name="tasks" table="task" cascade="delete" order-by="due_date, priority">
<key column="project_id"/>
<one-to-many class="org.appfuse.model.Task"/>
</set>
</class>
</hibernate-mapping>

Task.hbm.xml
<hibernate-mapping>
<class name="org.appfuse.model.Task" table="task">
<id name="id" column="id" unsaved-value="null">
<generator class="increment"/>
</id>
<property name="label" column="label" not-null="true"/>
<many-to-one name="requestedBy" column="requested_id" class="org.appfuse.model.User"/>
<many-to-one name="assignedTo" column="assigned_id" class="org.appfuse.model.User"/>
</class>
</hibernate-mapping>

User.hbm.xml
<hibernate-mapping>
<class name="org.appfuse.model.User" table="user">
<id name="id" column="id" unsaved-value="null">
<generator class="increment"/>
</id>
<property name="password" column="password" not-null="true"/>
<property name="firstName" column="first_name" not-null="true"/>
<property name="lastName" column="last_name" not-null="true"/>
</class>
</hibernate-mapping>

Gregg,
try this

Select proj from project proj
left join proj.tasks tasks join proj.manager mgr
where tasks.requestedBy = :userid or
tasks.assignedTo = :userid or
mgr.manager = :userid

Gregg,


Make sure that you replace requestedBy, assignedTo and manager in the following query are replaced by id

Select proj from project proj
left join proj.tasks tasks join proj.manager mgr
where tasks.id = :userid or
tasks.id = :userid or
mgr.id = :userid

It works it works!!

Thanks!