Some help please with a specific query

erik.jogi · **Posted:** Wed Mar 31, 2004 4:53 pm

Hello,

I have the following mapping:

Code:

<hibernate-mapping>
  <class name="Project" table="projects">
    <id name="id" unsaved-value="0">
      <generator class="identity"/>
    </id>
    <property name="name"/>
    <property name="comments"/>
    <property name="startDate" type="date"/>
    <property name="endDate" type="date"/>
    <list name="activities" table="activities" cascade="all">
      <key column="project_id"/>
      <index column="position"/>
      <composite-element class="ProjectActivity">
        <property name="description"/>
        <property name="timestamp" column="time_stamp"/>
      </composite-element>
    </list>
  </class>
</hibernate-mapping>

In other words there are Projects which can have several ProjectActivities in a list.

Now I need to display a list of all activities across all projects together with the name of the project, sorted by the activity timestamp.

I can do this quite easily in SQL:

Code:

SELECT a.time_stamp, a.description, p.name
FROM activities a, project p
WHERE a.project_id = p.id
ORDER BY t.time_stamp 

How do I specify in HQL that I want to sort based on the timestamp property of the elements of the activities property? And a similar question as to how to select the description property from that list?

dimitry · **Posted:** Wed Mar 31, 2004 6:55 pm

It may be smth. like that:

Code:

select a.timestamp, a.description, p.name 
  from Project p
  inner join p.activities a
  order by a.timestamp

erik.jogi · **Posted:** Thu Apr 01, 2004 12:08 am

Thank you, dimitry

This is exactly what I needed. This inner join with the list was something I couldn't figure out myself.