List of instances with minimal date of their group

Question

I'm working on a Java project, using Hibernate to administrate data on a SQL database. I try to fetch a list of instances from the Database, that have a minimal timestamp of the group they share. The group is modeled by a container.

Here is a minimal model sketch:

@Entity
@Table(name = "object")
public class Object implements Serializable{

    @Id
    @GeneratedValue(strategy = GenerationType.Auto) 
    long obj_id;

    @Column(name = "time_stamp", nullable = false)
    Date timestamp;

    @ManyToOne(fetch = FetchType.EAGER)
    @JoinColumn(name = "container_id", nullable = false)
    Container con;
}

@Entity
@Table(name = "container")
public class Container{

    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    long  con_id;

    @OneToMany(mappedBy = "container")
    List<object> obj_list;
}

So there are some objects with a timestamp and containers that group these objects.

For example, there are two containers, con_a and con_b:

Container con_a:
    con_id = 1
    obj_list = {obj_a, obj_b}

Container con_b:
    con_id = 2
    obj_list = {obj_c}

And three objects, obj_a, obj_b, obj_c:

Object obj_a
    obj_id = 1
    timestamp = 10
    con = con_a

Object obj_b
    obj_id = 2
    timestamp = 20
    con = con_a

Object obj_c
    obj_id = 3
    timestamp = 30
    con = con_b

The desired List in this example would look like:

List<Object> = {obj_a, obj_c}

I seem to move in a circle, as I do not even know where to "start" the query:

Criteria crit = session.createCriteria(Container.class). ...

or

Criteria crit = session.createCriteria(Object.class). ...

It seems both possible for me, but i just have no idea how to go on from any of those 2 possibilities.

Update [2014.07.11, 14:19]:

I tried and started the query with the Object class and used a Subquery:

Session session = getSession();
Transaction transaction = session.beginTransaction();

DetachedCriteria IdListOfGroupMinimum = DetachedCriteria.forClass(Object.class, "obj")

IdListOfGroupMinimum.createAlias("con.id", "containerId")
    .setProjection(
     .Projections.projectionList()
     .add(Projections.property("obj.id"))
     .add(Projections.min("obj.timestamp"))
     .add(Projections.groupProperty("containerId")))
    .setProjection(Projection.property("obj.id"));

Criteria objects = session.createCriteria(object.class, "obj")
objects.add(Subqueries.in("obj.id", IdListOfGroupMinimum));

List<Object> = objects.list();

But I received the following error:

javax.servlet.ServletException: org.hibernate.QueryException: not an association: id

I tried to do this:

SELECT * from Object
WHERE id IN (
    SELECT obj.id
    FROM Object obj
    INNER JOIN (
        SELECT obj.containerID, MIN(obj.timestamp) AS minimum 
        FROM Object obj 
        GROUP BY obj.containerID) subquery
    ON obj.containerID = subquery.containerID
    WHERE obj.timestamp = subquery.minimum
    )

score 0 · Accepted Answer · 2014-07-20T16:50:06.880

I found a solution for my problem which is probably not the most elegant one, but it works.

Mainly I used the SQL-Query that I already posted above:

 Session session = getSession();
 Transaction transaction = session.beginTransaction();

 //This query fetches the IDs of the smallest objects in each group with 
 //regard to the timestamp
 Query q = session.createSQLQuery(
                "SELECT obj.id FROM Object obj "
              + "INNER JOIN ( " 
                   + "SELECT obj.containerID, MIN(obj.timestamp) AS minimum "
                   + "FROM Object obj "
                   + "GROUP BY obj.containerID) subquery "
              + "ON obj.containerID = subquery.containerID "
              + "WHERE obj.timestamp = subquery.minimum "
              );

//This tells Hibernate that the result are values of type Long
q.addScalar("id", LongType.INSTANCE)

//Creates a list of the found IDs
@SuppressWarnings("unchecked")
List<Long> ids = q.list(); 

//Fetches all object with those IDs...
Criteria smallestOfEachGroup = session.createCriteria(Object.class)
                                      .add(Restrictions.in("id", ids);
//...and saves them in a list.
@SuppressWarnings("unchecked")
List<Object> desiredList = smallestOfEachGroup.list()  

try{
    transaction.commit();
} catch(HibernateException e) {
    transaction.rollback();
}

As all my sketches are not the real code, so there might be still naming errors.

Anyway, I hope this helps someone.

I still would be pleased by any more elegant solution.

Update [2014.07.20, 18:50]:

I found a solution that uses Hibernate Criteria exclusively :)

Session session = getSession();
Transaction transaction = session.beginTransaction();

//This subquery fetches the minimal timestamp of a container.
DetachedCriteria minOfGroup = DetachedCriteria.forClass(Object.class);
minOfGroup.add(Restrictions.eqProperty("con.con_id", "outerObject.con.con_id")
          .setProjection(Projections.min("timestamp"));

//This subquery fetches the IDs of all Objects, whose timestamp is minimal
//in their container.
DetachedCriteria groupwiseMin = DetachedCriteria.forClass(Object.class, "outerObject");
groupwiseMin.add(Subqueries.propertyEq("timestamp", minOfGroup));
            .setProjections(Projections.id())

//This subquery fetches all Objects whose IDs are fetched by the groupwiseMin
//query
Criteria groupwiseMinObjects = session.createCriteria(Object.class);
groupwiseMinObjects.add(Subqueries.propertyIn("obj_id", groupwiseMin));

List<Object> desiredObjects = groupwiseMinObjects.list();

try{
   transaction.commit();
} catch(HibernateException e) {
   transaction.rollback();
}

I think you can make this query even shorter, if you remove the groupwiseMinObjects query above replace the groupwiseMin query by:

Criteria anotherGroupWiseMinObjects = session.createCriteria(Object.class, "outerObject");
anotherGroupwiseMinObjects.add(Subqueries.propertyEq("timestamp", minOfGroup));

But I did not test that. In my original project I use several subqueries that converge in a single query. That means after some subqueries, there is a final query like:

Criteria finalQuery = session.createCriteria(Object.class);
finalQuery.add(Subqueries. (...) )
          (...)
          .add(Subqueries. (...) );

List of instances with minimal date of their group

1 Answers1