Find max revision of each entity less than or equa

Question

This might be simple, but unable to find a way. I am trying to find max revision of each entity less than or equal to given revision number.

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

Above code returns multiple revisions of same entity in descending order. I would like to get latest distinct revision of each entity less than or equal to given revision number.

As a workaround, I am filtering resultSet as shown below. I am hoping that this filtering can be done at AuditQuery itself.

    AuditQuery query = getAuditReader().createQuery().forRevisionsOfEntity(
            entityClass, false, false);
    query.add(AuditEntity.revisionNumber().le(revision));       
    query.addOrder(AuditEntity.revisionNumber().desc());
    List<?> list = query.getResultList();

    StringBuilder builder = new StringBuilder();
    EntityClass entity = null;
    Set<Long> entitySet = new HashSet<>();
    if (!list.isEmpty()) {
        for (int i = 0; i < list.size(); i++) {
            Object[] result = (Object[]) list.get(i);
            entity = (EntityClass) result[0];
            AuditRevision auditRevision = (AuditRevision) result[1];
            RevisionType operationType = (RevisionType) result[2];

            if (!entitySet.contains(entity.getId())) {
                //consider most recent revision of entity
                entitySet.add(entity.getId());
                builder.append(i + "-->");
                builder.append("id:" + entity.getId());
                builder.append(", auditRevision:" + auditRevision);
                builder.append(", operationType:" + operationType);
            }
        }
    }
    builder.append(", count:" + entitySet.size());

Solution:

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();

Answer 1

I was looking for something similar but found a much better way to get what I wanted. It's taken a while to work this out today, but it makes sense now!

In my case I wanted to get entities at a particular date in the past. In Envers this means at a particular revision, as the revision is universal across all entities, not per entity.

So, the idea is to find the revision number from the date, and then fetch all entities at that revision. I think this is similar to what you are after.

AuditReader reader = AuditReaderFactory.get(this.entityManager);

Number latestRevAtDate = reader.getRevisionNumberForDate(convertedRunDate);

reader.createQuery()
      .forEntitiesAtRevision(MyEntity.class, latestRevAtDate)
      .add(AuditEntity.property("someEntityProperty").le("someValue"))
      .getResultList()

You only the individual entities instead of multiple revisions of each entity, and you get all entities that were in the system at the time of that revision.

This post helped a bit: https://developer.jboss.org/message/625074#625074

Answer 2

I think what you are looking for the AuditEntitry.revisionNumber.max() constraint. This should maximize the revision number satisfying the nested constraints.

See also the documentation on that matter.