Overview

The intent for this feature is to add support for audit queries with constraints, projections, and sort operations based on joined entity relations.

Here is an example to illustrate:

Example use cases using an associated entity's property (Car.owner.name):

• find all Car entities at revision 1 with an owner named "John Doe" (inner join and constraint);
• find all Car entities at revision 1 that have no owner, or have an owner named "Jane Doe" (outer join and constrant);
• find the Car ID and its owner's name of each Car at revision 1 (inner join and projections);
• find all Car entities at revision 1, with or without an owner, ordered by owner name (outer join and sort).

Background:

Note: this feature has already been implemented by [HHH-11025] for EAP 7.1.0.

A new feature has been proposed that allows Envers audit data to be queried with specific where clauses on associated object.  While HQL-like queries are not supported for auditing Envers audit data, here is an example using Hibernate HQL to illustrate outer join, constraint, and sort functionality that would need to be added to the org.hibernate.envers.query.AuditQuery API:

"select c from Car c left join c.manufacturer m left outer join c.owner where m.name = :manufacturerName order by c.owner"

Envers requires the following functionality to be added to org.hibernate.envers.query.AuditQuery to support applying constraints. projections, and sort operations based on entity relations:

• ability to specify an inner or outer join on *-to-one entity associations;
• ability to traverse to a *-to-one associated entity and use its properties in audit query expressions;
• ability to use entity aliases for specifying projections, constraints and sort operations.

Issue Metadata

EAP ISSUE: https://issues.jboss.org/browse/EAP7-293

RELATED ISSUES:

The following have already been implemented upstream in Hibernate ORM 5.1.0 and 5.2.0 (Hibernate Issue Tracker):

[HHH-3555] Extend the Envers query system with the ability to traverse associations (limited to inner joins)

[HHH-16] Explicit joins on unrelated classes - Hibernate Issue Tracker

The following have already been released upstream in Hibernate ORM 5.2.0 (Hibernate Issue Tracker):

[HHH-10762] Implement left-joins for relation traversion in audit queries

[HHH-9178] Querying audited entities with embeddables fails with 'org.hibernate.QueryException: could not resolve property

[HHH-8070] Support "in" expression in AuditRelatedId - Hibernate Issue Tracker

The following backports HHH-10762, HHH-9178, HHH-8070 to Hibernate ORM 5.1 branch and was released in Hibernate ORM 5.1.1:

[HHH-11025] Backport: Implement left-joins for relation traversal in audit queries

Commit: https://github.com/hibernate/hibernate-orm/commit/8206ab3a50adcd3f468086f6ed0e6304551230ce

Documentation: https://docs.jboss.org/hibernate/orm/5.1/userguide/html_single/Hibernate_User_Guide.html#envers-querying-entity-relation-jobs

DEV CONTACTS: Gail Badner (primary), Chris Cranford (developer)

QE CONTACTS: Jan Martiška

AFFECTED PROJECTS OR COMPONENTS: Hibernate Envers 5.1.x (in Hibernate ORM component)

OTHER INTERESTED PARTIES: TBD

Requirements

Hard Requirements

These items must be satisfied in order to have a satisfactory feature.

• Traverse *-to-one associations
  • Traverse a *-to-one association using an INNER JOIN:

Example: AuditQuery query = getAuditReader().createQuery()

.forEntitiesAtRevision( Car.class, 1 )

.traverseRelation( "owner", JoinType.INNER );

• Traverse a *-to-one association using a LEFT JOIN:

Example: AuditQuery query = getAuditReader().createQuery()

.forEntitiesAtRevision( Car.class, 1 )

.traverseRelation( "owner", JoinType.LEFT );

• Traverse nested associations;

Example: AuditQuery query = getAuditReader().createQuery()

.forEntitiesAtRevision( Car.class, 1 )

.traverseRelation( "owner", JoinType.INNER )

.traverseRelation( "address", JoinType.INNER );

• Traverse associations and navigate back up an entity graph to traverse other entity associations

(up method is used to navigate back up the entity graph)

Example: AuditQuery query = getAuditReader().createQuery()

.forEntitiesAtRevision( Car.class, 1 )

.traverseRelation( "owner", JoinType.INNER )

.up()

.traverseRelation( "manufacturer", JoinType.INNER );

• Constraints based on an associated entity properties
  • Constraint on an associated entity's property

Example: AuditQuery query = getAuditReader().createQuery()

.forEntitiesAtRevision( Car.class, 1 )

.traverseRelation( "owner", JoinType.INNER )

.add( AuditEntity.property( "name" ).like( "Joe%" ) );

• Constraints using entity aliases

Example: AuditQuery query = getAuditReader().createQuery()

.forEntitiesAtRevision( Car.class, 1 )

.traverseRelation( "owner", JoinType.INNER, "p" )

.traverseRelation( "address", JoinType.INNER, "a" )

.up()

.up()

.traverseRelation( "manufacturer", JoinType.INNER, "m" )

.add(

AuditEntity.disjunction()

.add( AuditEntity.property( "p", "age" ).eq( 20 ) )

.add( AuditEntity.property( "a", "streetNumber" ).eq( 1234 ) )

.add( AuditEntity.property( "m", "name" ).eq( "Chevrolet" ) )

);

• Sort operations based on associated entity properties

Example: AuditQuery query = getAuditReader().createQuery()

.forEntitiesAtRevision( Car.class, 1 )

.traverseRelation( "owner", JoinType.INNER, "o" )

.up()

.traverseRelation( "manufacturer", JoinType.INNER, "m" )

.addOrder( AuditEntity.property( "o", "name" ).asc() )

.addOrder( AuditEntity.property( "m", "name" ).asc() );

Nice-to-Have Requirements

Support for audit queries with constraints, projections, and sort operations based on *-to-many entity relations.