# AuditQuery and relationships

**URL:** <https://discourse.hibernate.org/t/auditquery-and-relationships/2372>\
**Category:** Hibernate ORM\
**Created:** [February 26, 2019, 4:26pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372 "2019-02-26T16:26:06Z")\
**Posts on this page:** 20\
**Page:** 1

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [February 26, 2019, 4:26pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/1 "2019-02-26T16:26:06Z")

</div>

Hello,

Can relations be queried in the current version (5.x)? For example, would this work:

```auto
query.traverseRelation("Child", JoinType.LEFT, "ChildAlias")
					.add(AuditEntity.property("ChildAlias", "name").like(some-value));

```

Also, not sure if ‘Child’ in this case is the relationship name (in the master Entity), or the child entity name?

Thanks!

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 26, 2019, 5:09pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/2 "2019-02-26T17:09:32Z")

</div>

I suppose you are talking about Envers. Check out the [User Guide](http://docs.jboss.org/hibernate/orm/5.3/userguide/html_single/Hibernate_User_Guide.html#envers-queries) for many query examples related to Envers.

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [February 26, 2019, 5:25pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/3 "2019-02-26T17:25:15Z")

</div>

Yes, i was not specific enough. This is what I found in the docs you sent the link for:

“The main limitation of the current queries implementation is that you cannot traverse relations. You can only specify constraints on the ids of the related entities, and only on the “owning” side of the relation. This, however, will be changed in future releases.”

BTW, the last sentence was there in version 3.\* …

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [February 26, 2019, 5:35pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/4 "2019-02-26T17:35:12Z")

</div>

Cam anyone suggest a workaround? I just need to query for the One side properties in a ManyToOne relationship?

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 26, 2019, 6:05pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/5 "2019-02-26T18:05:39Z")

</div>

Maybe @Naros can help you with that.

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [March 3, 2019, 5:38pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/6 "2019-03-03T17:38:45Z")

</div>

Thanks Vlad!  
I am not sure, however, how to send a question to a member of this forum directly?

---

<div class="post-metadata">

**Author:** ![Naros](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/naros/32/19_2.png) [@Naros](https://discourse.hibernate.org/u/Naros)\
**Post date:** [March 4, 2019, 3:09pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/7 "2019-03-04T15:09:02Z")

</div>

Hi @gbakalar!

In Hibernate Envers 5.x, the only traversal that is currently supported are those that are _to-one_ mappings. We intend to support the _to-many_ association traversal in Hibernate Envers 6.0.

That said, lets assume we have the following mappings:

```auto
@Entity
@Audited
public class Parent {
  @Id
  @GeneratedValue
  private Integer id;
  private String name;
  @OneToMany(mappedBy = "parent")
  private List<Child> children; 
}

@Entity
@Audited
public class Child {
  @Id
  @GeneratedValue
  private Integer id;
  private String name;
  @ManyToOne
  private Parent parent;
}

```

We want to find all the revisions of `Child` where it has the name `C1` and has a `Parent` with the name `P1`.

```auto
List results = auditReader.createQuery()
    .forRevisionsOfEntity( Child.class, true, false )
    .traverseRelation( "parent", JoinType.INNER )
     // Apply prdicate on the `Parent` entity.
    .add( AuditEntity.property( "name" ).eq( "P1" ) )
    .up()
     // Apply predicate on the `Child` entity.
    .add( AuditEntity.property( "name" ).eq( "C1" ) )
    .getResultList();

```

As you can see from my example, the first argument to `traverseRelation` is the property name of the attribute you want to traverse relative to the current class as part of the context. In other words, I am saying that we want to traverse the `parent` property that is in the `Child` class and then filter on a property in `Parent` called `name` with the value of `P1`.

Hopefully that clears up any confusion.

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [March 4, 2019, 3:21pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/8 "2019-03-04T15:21:32Z")

</div>

Thank you very much! This is exactly what I need. To have functional dependencies under control, that is definitely great first step. One thing - how do i query for transitively dependent objects? For example:

Child-\>Parent-\>GrandParent

Would this work?  
List results = auditReader.createQuery()  
.forRevisionsOfEntity( Child.class, true, false )  
.traverseRelation( “parent”, JoinType.INNER )  
.traverseRelation( “grand-parent”, JoinType.INNER )  
.add( AuditEntity.property( “gp-name” ).eq( “GP1” ) )  
.up()

Would this work?

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [March 4, 2019, 3:25pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/9 "2019-03-04T15:25:27Z")

</div>

Forgot to ask one more thing - I assume i should be able to set the order to properties that belong to Parent. Is this correct?  
Thanks!

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [March 4, 2019, 4:54pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/10 "2019-03-04T16:54:51Z")

</div>

@Naros I am getting exception: “… not yet implemented …”. In what exact version is your example supported?

Thanks!

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [March 4, 2019, 5:03pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/11 "2019-03-04T17:03:06Z")

</div>

java.lang.UnsupportedOperationException: Not yet implemented for revisions of entity queries  
I am using envers version 5.4.1

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [March 4, 2019, 5:17pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/12 "2019-03-04T17:17:38Z")

</div>

@Naros - I apologize, but this is urgent and i am not sure if what I need is really supported in 5.4.1?

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [March 4, 2019, 5:19pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/13 "2019-03-04T17:19:10Z")

</div>

This is how the implementation looks like:  
@Override  
public AuditAssociationQuery\<? extends AuditQuery\> traverseRelation(String associationName, JoinType joinType) {  
throw new UnsupportedOperationException( “Not yet implemented for revisions of entity queries” );  
}

When you wrote your example, you must have had certain implementation in mind - certainly not the one in 5.4.1 - right?

---

<div class="post-metadata">

**Author:** ![Naros](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/naros/32/19_2.png) [@Naros](https://discourse.hibernate.org/u/Naros)\
**Post date:** [March 4, 2019, 6:34pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/14 "2019-03-04T18:34:33Z")

</div>

> [@gbakalar](#):
>
> how do i query for transitively dependent objects? For example:
> 
> Child-\>Parent-\>GrandParent
> 
> Would this work?
> 
> ```auto
> List results = auditReader.createQuery()
> .forRevisionsOfEntity( Child.class, true, false )
> .traverseRelation( "parent", JoinType.INNER )
> .traverseRelation( "grand-parent", JoinType.INNER )
> .add( AuditEntity.property( "gp-name" ).eq( "GP1" ) )
> .up()
> 
> ```

Yes, so long as the relation is a _to-one_, you can simply traverse the object-graph as nested as you need and apply the appropriate predicates to filter the results.

> [@gbakalar](#):
>
> I assume i should be able to set the order to properties that belong to Parent. Is this correct?

I’m not clear on what you’re asking here.

> [@gbakalar](#):
>
> When you wrote your example, you must have had certain implementation in mind - certainly not the one in 5.4.1 - right?

My apologies, you’re correct the `RevisionsOfEntityQuery` does not yet support this feature; I simply wrote an example to illustrate the syntax and overlooked that. The other two query implementations should work, `EntitiesModifiedAtRevisionQuery` and `EntitiesAtRevisionQuery`.

I assume you need to be able to fetch a list of revisions based on predicates rather than knowing the revision numbers ahead of time?

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [March 4, 2019, 7:12pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/15 "2019-03-04T19:12:49Z")

</div>

About the order - I need to sort on a property of ‘toOne’ entity.  
About the query - Yes, I need a list of revisions filtered by a property of ‘toOne’ entity.

Do you have any idea when will this be available? I would even take ‘code in development’, as long as it can be used for couple of scenarios I need! (that’s how desperate i am 🙂

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [March 6, 2019, 6:02pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/16 "2019-03-06T18:02:10Z")

</div>

@Naros - Any hint here?

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [April 7, 2019, 11:53pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/17 "2019-04-07T23:53:28Z")

</div>

Is this implemented in Alpha2? If not, is there any info when will it be implemented?

---

<div class="post-metadata">

**Author:** ![Naros](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/naros/32/19_2.png) [@Naros](https://discourse.hibernate.org/u/Naros)\
**Post date:** [April 9, 2019, 3:04pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/18 "2019-04-09T15:04:01Z")

</div>

Hi @gbakalar

A number of the new Envers features such as _to-many_ traversal support is not yet in 6.0’s alpha stream as we’ve been more focused on getting `hibernate-core` stable with all the necessary features. It’s really difficult to flush out and implement behavior in a submodule when the main module does not yet support and implement all the necessary features to build upon in the first place, so its all very much a work in progress stream of changes.

> [@gbakalar](#):
>
> About the order - I need to sort on a property of ‘toOne’ entity.

I will need to double check if this is something the feature branch supports. Is the _to-one_ order-by the only sorting you need or do you need sorting on other parts of the domain model, such as the root/parent too?

> [@gbakalar](#):
>
> About the query - Yes, I need a list of revisions filtered by a property of ‘toOne’ entity.

I’ll take a look and see if we cannot get that implemented in 5.x for `RevisionsOfEntity` queries to work around the `UnsupportedOperationException` that the code currently throws. I can’t give you a specific timeline of when I’ll get it working as my time is split among several development projects right now, but I’ll do my best to get it done asap.

---

<div class="post-metadata">

**Author:** ![gbakalar](https://avatars.discourse-cdn.com/v4/letter/g/43a26b/32.png) [@gbakalar](https://discourse.hibernate.org/u/gbakalar)\
**Post date:** [April 9, 2019, 3:47pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/19 "2019-04-09T15:47:15Z")

</div>

> [@Naros](#):
>
> Is the _to-one_ order-by the only sorting you need or do you need sorting on other parts of the domain model, such as the root/parent too?

I am not sure I understand this question. If I have relationship Child-\>Parent, and columns Child.c1, Parent.p1, the minimum I would need is sort on either Child.c1, or Parent.p1 (no need together, but just one of them).

> [@Naros](#):
>
> I’ll take a look and see if we cannot get that implemented in 5.x for `RevisionsOfEntity` queries to work around the `UnsupportedOperationException` that the code currently throws. I can’t give you a specific timeline of when I’ll get it working as my time is split among several development projects right now, but I’ll do my best to get it done asap.

Thank you very much!

---

<div class="post-metadata">

**Author:** ![Naros](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/naros/32/19_2.png) [@Naros](https://discourse.hibernate.org/u/Naros)\
**Post date:** [April 9, 2019, 4:50pm UTC](https://discourse.hibernate.org/t/auditquery-and-relationships/2372/20 "2019-04-09T16:50:28Z")

</div>

> [@gbakalar](#):
>
> I am not sure I understand this question. If I have relationship Child-\>Parent, and columns Child.c1, Parent.p1, the minimum I would need is sort on either Child.c1, or Parent.p1 (no need together, but just one of them).

What I was trying to understand was whether you need to effectively write an order-by fragment that looked something like `"ORDER BY child.fieldX DESC, parent.fieldA ASC"` . This is what I am not sure if the current implementation is capable of supporting when it comes to traversals.

[Next page](https://discourse.hibernate.org/t/auditquery-and-relationships/2372.md?page=2)