The way revision data is stored each object affected by a revision gets a separate record in the _AUD table. So when I search for revisions affecting object A, I will get back the entry where revision is 3 for object B, but if objects A and/or C were also changed in revision 3, those entries aren't returned, giving the impression that B was the only object modified in that revision. What I'm trying to do is for each revision affecting object B, return all objects affected by that revision.
something_AUD desired actual id|REV id|REV id|REV ------------- ------- ------ A|1 B|2 B|2 B|2 B|3 B|3 B|3 C|3 C|3
I've been trying to do this by running an initial query to find the relevant revisions:
AuditQuery query = AuditReaderFactory.get(entity.em()).createQuery()
.forRevisionsOfEntity(type, false, true)
.add(AuditEntity.id().eq(entity.id));
and then running the following query for each result:
int rev_id = ((RevisionData) data[1]).getId();
AuditQuery q = AuditReaderFactory.get(JPA.em()).createQuery()
.forRevisionsOfEntity(type, false, true)
.add(AuditEntity.revisionNumber().eq(rev_id));
List<Object[]> real_data = q.getResultList();
but this is resulting in a QuerySyntaxException:
Duplicate definition of alias 'r' [select e, r, r from models.AgentShift_AUD e, models.RevisionData r, models.RevisionData r where e.originalId.REV.id in (:_p0) and e.originalId.REV.id = r.id and e.originalId.REV.id in (:_p1) and e.originalId.REV.id = r.id order by e.originalId.REV.id asc, e.originalId.REV.id asc]
I've tried several variations of this which have all led to the same "Duplicate definition of alias 'r'". Is there anything else I can try?