Listing latest revision of each entity with envers

Question

I am trying to retrieve the latest revision of all entities, that have not been deleted. Doing this in SQL is very simple with an subselect:

select * from article_aud aud1
where rev in
 (select max(rev) from article_aud aud2
  where aud1.id = aud2.id)
and revtype < 2

But I do not have a clue, how to implement this via the envers API. I started with AuditReader but did not find a way, to select distinct objects

public List<Object[]> findLatestArticleRevisions(){
    List<Object[]> results = (List<Object[]>) getJpaTemplate().execute(new AuditReaderCallback() {
        @Override
        public Object doInAuditReader(AuditReader auditReader) {
            return auditReader.createQuery().forRevisionsOfEntity(Article.class, false, false)
                // TODO select distinct on entities
                .addOrder(new PropertyAuditOrder(new RevisionNumberPropertyName(), false))
                .getResultList();
        }
    });

    return results;
}

Important: I want to do this in one or at least two queries, because I have got many articles (entities) with many revisions.

Thanks a lot!

Answer 1

We need to use fix of https://hibernate.atlassian.net/browse/HHH-7827 i.e. AuditEntity.revisionNumber().maximize().computeAggregationInInstanceContext().

    AuditQuery query = getAuditReader().createQuery().forRevisionsOfEntity(
            entityClass, false, false);
    query.add(AuditEntity.revisionNumber().le(revision));
    query.add(AuditEntity.revisionNumber().maximize()
            .computeAggregationInInstanceContext());
    query.addOrder(AuditEntity.revisionNumber().desc());
    return query.getResultList();

Also refer to:

Find max revision of each entity less than or equal to given revision with envers

Can hibernate envers return the latest revision of all entities of a specific type?