Listing latest revision of each entity with envers

user_831145 0
Viewed 11912

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!

hibernate-envers hibernate sql java
4 Answers

The answer from @charybr works, IF you put the parathess the right way. Just add your filters that you need for the subset you want to select from directly to the maximize query part. In this way the select statements will also appear in the subquery.

    AuditQuery query = auditReader.createQuery()
            .forRevisionsOfEntity(Event.class, true, true);

    query.setFirstResult((pageable.getPageNumber()) * pageable.getPageSize());
    query.setMaxResults(pageable.getPageSize());
    query.add(
                AuditEntity.revisionNumber().maximize()
                .computeAggregationInInstanceContext()
                    .add(AuditEntity.property("critical").eq(true))
             );
    query.getResultList();

A bit late, but might help someone.

user_74220380
Related