How can I execute a raw jpa query with AuditReader?

[nycdude777]

Hi,

I need to run a "time slice" query using auditreader as of a point in time on a joined query. 

The limitation that I ran into is that when you use AuditReader, you can't execute an arbitrary jpa query.

So,

List myEntities= getAuditReader().createQuery()
    .forEntitiesAtRevision(MyEntity.class, 12)
    .getResultList();

This gives me all the entities as of revision.  The only way to set a filter is:

.add(AuditEntity.relatedId("address").eq(addressId))

I would like to set a filter criteria based on values in a joined table, like this:

select ent from MyEntity ent JOIN Address addr

WHERE addr.streetNumber>100 AND addr.streetNumber<=199

Ideally, it should allow something like this:

List myEntities= getAuditReader().createQuery("select ent from MyEntity ent JOIN Address addr

                                                                       WHERE addr.streetNumber>100 AND addr.streetNumber<=199")
    .forEntitiesAtRevision(12)
    .getResultList();

Has anyone ever encountered this problem?  What's the workaround?

Thanks,

Rick

[adamw]

Hello,

currently this is a limitation of Envers, which I think is even documented - you cannot traverse relations in queries. I hope to fix this after the end-revision column support is implemented, some time in the future. For now, the queries would just be too complicated.

Adam