QueryDSL Access Id column without join

Question

Description

We have a case where we are querying out historical information and we'd like to use QueryDSL and not raw SQL. The data in the historical table contains a the id of the original record (Hibernate ENVERs) and in the event of a deletion, the actual record is removed but the ENVERs record contains the original ID.

Issue

The issue we are facing is that since the entity that is creating the Q class only references the foreign key, this forces a join in the SQL. Due to using Hibernate, we are unable to reference the column for multiple fields.

We were hoping there is a way for QueryDSL to reference the raw id column without performing the join and without changing the entity itself to only include the ID instead of the mapped reference to Foo.

Data\Example (psuedo code)

Entities

@Entity
@Table=name = "foo")
public class Foo {
    @Id
    @Column(name = "foo_id", nullable = false)
    private Long fooId;

}

@Entity
@Table=name = "foo_bar")
public class FooBar {
    @Id
    @Column(name = "foo_bar_id", nullable = false)
    private Long fooBarId;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "foo_id")
    private Foo foo;
}

Forced Query

HibernateQuery dsl = createHibernateQuery();
dsl
    .from(qFooBar)
    .where(qFooBar.foo.fooId.eq(id))
;

Ideal Query

HibernateQuery dsl = createHibernateQuery();
dsl
    .from(qFooBar)
    .where(qFooBar.fooId.eq(id))
;

Additional Information

In the real world use case the query is a lot more complicated so we cannot use purely ENVERs' API for querying.

Versions

• Spring: 4.2.8.RELEASE
• Hibernate: 4.3.11.Final
• QueryDSL: 3.7.4
• Java 7

Answer 1

I can't speak necessarily to QueryDSL; however, the traditional Envers API does offer you this functionality out of the box.

Given that your example is based upon the association's identifier, you can make use of the relatedId predicate to accomplish your goal:

List results = auditReader.createQuery()
    .forRevisionsOfEntity( FooBar.class, true, false )
    .add( AuditEntity.relatedId( "foo" ).eq( fooId ) )
    .getResultList();

This will return all FooBar instances from the audit history (excluding deletions) where your FooBar is related to a Foo that used the identifier fooId.

The only restriction here is the query must apply a restriction based on the association's identifier only, not a non-identifier based property.

The reason for asking which version is because in the 5.2.x branch when we introduced the Envers Query traversal API, which extends the query API with ways to apply predicates based on any to-one associated property anywhere in the entity hierarchy.

For example, the follow shows locating FooBar entities where some random property on the associated Foo is someValue.

List results = auditReader.createQuery()
    .forRevisionsOfEntity( FooBar.class, true, false )
    .traverseRelation( "foo", JoinType.INNER )
    .add( AuditEntity.property( "somePropertyOnFoo" ).eq( someValue ) )
    .up()
    .getResultList();

So unless there is a specific use case why you cannot accomplish the query with the Envers Query API, I don't believe your use of the QueryDSL library is necessary and the native AuditReader interface provides all the tools you need.