Filtering entities on a version field with JPA Criteria-API

Question

I have an entity with an embedded id which contain an id and version field.

@Entity
@Table(name = "MyEntity")
public class MyEntity {
    @EmbeddedId
    private MyEntityEmbeddedId compositeId;

    @Column
    private DateTime begin;

    @Column
    private DateTime end;

}

@Embeddable
public class MyEntityEmbeddedId {
    @Column(name = "ID", nullable = false, updatable = false)
    private Long id;

    @Column(name = "VERSION", nullable = false, updatable = false)
    private Long version;

    public MyEntityEmbeddedId () {}

}

I need to query the database with some criteria as filter for the entity. For example, I will request with a date and a list of id.

List<MyEntity> listEntities = getMyEntities(List<Long> ids, DateTime date);

I succeed to create a query which retreives all entities for which their id is in the ids list and that the provided date is between the "begin" and "end" date properties of MyEntity.

But as this entity has a composite id, there are many MyEntity with the same "id" that may correspond to the request parameters. I want to add another filter to retreive the MyEntity which have the highest "version" number if there are many with the same "id".

Here is an example of what might be in the database:

+------+-----------+--------------------+
| id   | version   | began    |   end   |
+------+-----------+--------------------+
| 1    | 1         | ...      |   ...   |
| 1    | 2         | ...      |   ...   |
| 1    | 3         | ...      |   ...   |
| 2    | 1         | ...      |   ...   |
| 2    | 2         | ...      |   ...   |
+------+-----------+--------------------+

If all the previous database records correspond to the filtering parameters, I need to get only these one because they are those with the highest version number for their corresponding id:

+------+-----------+--------------------+
| id   | version   | began    |   end   |
+------+-----------+--------------------+ 
| 1    | 3         | ...      |   ...   |
| 2    | 2         | ...      |   ...   |
+------+-----------+--------------------+

Currently, I do the request but I filter the version within a method inside my service but I would prefer it if the version was already filtered in the database request. I don't need to retreive records that will be discarded after the version was filtered.

I'm searching a way to make self-join with JPA Criteria-API.

In the following link, it seems that the accepted answer will resolve my problem by I'm wondering how I would translate the suggested SQL in JPA Criteria-API.

SQL Select only rows with Max Value on a Column

select yt1.*
from yourtable yt1
left outer join yourtable yt2
on (yt1.id = yt2.id and yt1.rev < yt2.rev)
where yt2.id is null;

My issue is when I'm trying to create the join. You need to provide the property from the first entity which link to the second entity in the join. As I want to make a self-join, there is no property inside my entity that point to itself.

Join<MyEntity, MyEntity> selfJoin = root.join(MyEntity_.???);

As you see, I'm using a static metamodel of MyEntity. It there a way to make the self-join with JPA Criteria-API?

Or maybe there is another way to build my request than with a self left-join as suggested in the other stackoverflow question.

I'm using Spring Data JPA Specification to build my query with Predicate. The findAll(Specification) is then used. I allready have two others Specification methods to generate predicate for the withIds() and withDate(). This let me create queries with the "undetermined number" of parameters provided by the user.

public static Specification<MyEntity> withIdAndDate(final List<Long> ids, 
        final DateTime date) {
    return new Specification<MyEntity>() {

        @Override
        public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, 
                CriteriaBuilder cb){
            Join<MyEntity, MyEntity> selfJoin = root.join();
            Predicate pIds = withIds(ids).toPredicate(root, query, cb);
            Predicate pDate = withDate(date).toPredicate(root, query, cb);

            return cb.and(pIds, pDate);
        }

    };
}

Thanks a lot for your help and suggestion!

Answer 1

Finally, I succeed to find a way to filter my MyEntity to the lattest version with JPA Criteria API so I'll shared with you my code, hoping it will help someone here.

public static Specification<MyEntity> withIdAndDate(final List<Long> ids, 
        final DateTime date) {
    return new Specification<MyEntity>() {

        @Override
        public Predicate toPredicate(Root<MyEntity> root, CriteriaQuery<?> query, 
                CriteriaBuilder cb){

            Subquery<Long> subqueryVersion = query.subquery(Long.class);
            Root<MyEntity> entity1 = subqueryVersion.from(MyEntity.class);

            Expression<Long> expMaxVersion = cb.greatest(entity1
                .get(MyEntity_.compositeId).get(MyEntityEmbeddedId_.version));
            Expression<Long> expIdRoot = root.get(MyEntity_.compositeId)
                .get(MyEntityEmbeddedId_.id);
            Expression<Long> expIdEntity1 = entity1 .get(MyEntity_.compositeId)
                .get(MyEntityEmbeddedId_.id);
            Expression<Long> expVersionRoot = root.get(MyEntity_.compositeId)
                .get(MyEntityEmbeddedId_.version);

            Predicate pId = cb.equal(expIdRoot, expIdEntity1);
            Predicate pIds = withIds(ids).toPredicate(entity1, query, cb);
            Predicate pDate = withDate(date).toPredicate(entity1, query, cb);

            subqueryVersion.select(expMaxVersion);
            subqueryVersion.where(pId, pIds, pDate);

            Predicate pQuery = cb.equal(expVersionRoot, subqueryVersion);

            return cb.and(pQuery);
        }

    };
}

In the following link, the accepted answer help me to build my query in JPQL and after, I succeed to translate it to JPA Criteria API. I remove the last part of the query which is the "AND NOT EXISTS" part. JPA Select latest instance for each item