Get max revision less than to a particular revision with envers

Question

The same question I found with this Link

It is working fine but the problem is while I am using Join, it is not giving single result, Its returning all Revision less than of given revision.

My code is as below

AuditReader reader = AuditReaderFactory.get(session);
AuditQueryCreator audQueryCreator = reader.createQuery();

AuditQuery query_cusTagInst = audQueryCreator.forEntitiesAtRevision(CustomTagInstance.class, revision_Id)
                .add(AuditEntity.revisionNumber().le(revision_Id))
                .traverseRelation("instrument", JoinType.INNER)
                .add(AuditEntity.revisionNumber().maximize().computeAggregationInInstanceContext())
                .add( AuditEntity.property( "instrumentId" ).eq( id ) );
CustomTagInstance customTagInst = null;
List list_cusTagInst = query_cusTagInst.getResultList();
for(int i=0; i<list_cusTagInst.size(); i++){
    customTagInst = (CustomTagInstance) list_cusTagInst.get(i);
}

And

@Audited
@Table(name = "CUSTOM_TAG_INSTANCE")
public class CustomTagInstance implements java.io.Serializable {

private Long tagInstanceId;
private Instrument instrument;

@Id
@Column(name = "TAG_INSTANCE_ID", unique = true, nullable = false, precision = 22, scale = 0)
public Long getTagInstanceId() {
    return this.tagInstanceId;
}

public void setTagInstanceId(Long tagInstanceId) {
    this.tagInstanceId = tagInstanceId;
}

@ManyToOne(fetch = FetchType.LAZY)
@JoinColumn(name = "INSTRUMENT_ID")
public Instrument getInstrument() {
    return this.instrument;
}

public void setInstrument(Instrument instrument) {
    this.instrument = instrument;
}

Please tell me is there anything that I need to use to get only max revision Less than to given revision.

Are you expecting to get the RevisionNumber as the result or the actual entity instance? Furthermore, IDK what this `computeAggregationInInstanceContext` is? The `#max()` method is only applicable for projection queries. — Naros, Apr 27 '17 at 13:07
No, I don't want only max revision. I am trying to find max revision of each entity less than or equal to given revision number. I have added link to my question that is similar to my question. But here I have to use **Join** and after adding join, its giving multiple records. — V.Dev, Apr 28 '17 at 09:33
And **computeAggregationInInstanceContext** I found it with this [Link_1](https://hibernate.atlassian.net/browse/HHH-7827]) and from this [Link_2](http://stackoverflow.com/questions/25723323/find-max-revision-of-each-entity-less-than-or-equal-to-given-revision-with-enver) — V.Dev, Apr 28 '17 at 09:35
Thanks, I had forgotten about that since I hardly use it :). — Naros, Apr 28 '17 at 12:23

score 0 · Answer 1 · answered Apr 28 '17 at 12:43

If you look at this from a pure SQL perspective, what you're describing is trying to acquire a value or aggregate of values that match a particular set of predicates. That automatically implies a projection query.

The solution here would be something like:

final AuditQuery query = AuditReaderFactory.get( session )
  .createQuery()
  .forEntitiesAtRevision( CustomTag.class, revisionId )
  .add( AuditEntity.revisionNumber().le( revisionId ) )
  .traverseRelation( "instrument", JoinType.INNER )
  .add( AuditRevisionNumber().maximize().computeAggregationInInstanceContext() )
  .add( AuditEntity.property( "instrumentId" ).eq( id ) )
  .up()
  .addProjection( AuditEntity.selectEntity( true ) )
  .addOrder( AuditEntity.revisionNumber().desc() )
  .setMaxResults( 1 );

The key parts that you were missing are the last three lines of the query.

The addProjection( AuditEntity.selectEntity( true ) ) tells Envers to generate a distinct on the entity instances themselves so that the return values are unique entity instances. This should avoid any issues with joins/duplicates.

We then add an addOrder( AuditEnttiy.revisionNumber().desc() ) order by clause having Envers order the results in descending order. We do this because the goal is to get a single row which has a revision number that is less-than or equal-to revisionId. By doing this, that row will be the first row in our result set.

We then apply a setMaxResults( 1 ). Based on the goal, this goes hand-in-hand with the order by clause we added as this asks Hibernate to only return the single row of interest, the one at the top of the result set which has a revision number less-than or equal-to revisionId.

HTH.

Thank you so much for explaining too good, but while adding `addProjection( AuditEntity.selectEntity( true ) )` query result is giving **Instrument object** (to which we are performing Join), while It should give **CustomTagInstance** object. I thought this might be because of **JoinType** so I checked with Left join too but the result is same, Then I removed this line, after that I am getting single result but that is not max revision number record less-than or equal-to given revision Id , Its picking some random value less than of given number, while I want max — V.Dev, Apr 29 '17 at 09:56
Is order matters ??? Means I changed the order of your code, after that its giving fine result, as I did `AuditQuery query_cusTagInst = audQueryCreator.forEntitiesAtRevision(CustomTagInstance.class, revision_Id).add(AuditEntity.revisionNumber().lt(revision_Id)).addOrder( AuditEntity.revisionNumber().desc() ) .traverseRelation("instrument", JoinType.INNER).add(AuditEntity.property( "instrumentId" ).eq( id )) .setMaxResults( 1 );` — V.Dev, Apr 29 '17 at 10:22

Get max revision less than to a particular revision with envers

1 Answers1