Query on period-based many-to-many relation

dataminer · **Joined:** Mon Nov 09, 2009 8:20 pm **Posts:** 1

Suppose there are two entities, Developer and Team. A Developer works on a Team for some time and then switches to another Team. So there is also an entity DeveloperTeam that has dateStart and dateEnd attributes (dateEnd may be null).

I would like to fetch all Developers from the database along with their current Teams.

Example:

Code:

> select * from DeveloperTeam;
id  | developer |      datestart      |       dateend       |  role  | team 
----+-----------+---------------------+---------------------+--------+------
  1 |         1 | 2009-02-04 00:00:00 | 2009-04-01 00:00:00 | ROLE_A |    1
  2 |         1 | 2009-04-01 00:00:00 |                     | ROLE_B |    2
  3 |         2 | 2008-02-06 00:00:00 | 2009-01-01 00:00:00 | ROLE_B |    5
  4 |         2 | 2009-01-01 00:00:00 |                     | ROLE_C |    3

Expected query result would be a set of two DeveloperTeam objects, id=2 and id=4, plus one DeveloperTeam object with id=null for each Developer who has not been assigned to any Team.

How can this be done? I've tried building a query that selects max(dateStart) and groups the results by developer, but weirdly that returned a set of Object instead of DeveloperTeam.

Thanks in advance for any suggestions!

mmerder · **Posted:** Tue Nov 10, 2009 6:51 am

Does it really have to be one single query?

In two queries it's easy:

Code:

select t from DeveloperTeam t where t.dateEnd is null

Code:

select d from Developer d where d is not in (select t.d from DeveloperTeam t where t.dateEnd is null)

If you want to use a single query or you really need DeveloperTeam-objects instead of developers in the second query you will need a constructorwithin your select.