Retrieve by date

[amarsingh]

How can I query by date and not by revision number.

Use Case:

Give me Person object as on Nov 23, 2008 12:20 PM.

[amarsingh]

Please note the version I am using is:

Envers 1.1.0 compatible for Hibernate 3.2.6

[adamw]

Hello,

in 1.1 you can use the getRevisionNumberForDate(Date) in VersionsReader, to find a revision number for a given date. You cannot unforutnately use a date directly in a query.

In trunk, you can use the following restriction in your query:
AuditEntity.revisionProperty("timestamp").eq(your value)

--
Adam

[amarsingh]

Thanks. Yeah, your solution was what I concluded with.

[desol4tion]

Hi,

i would like to do the same thing :

getAuditReader().createQuery().forRevisionsOfEntity(Clazz.class, true, false)

                        .add(AuditEntity.revisionProperty("timestamp").maximize().add(AuditEntity.id().eq(clazz.getId()))

                                .add(AuditEntity.revisionProperty("timestamp").le(date))).getSingleResult();

but i dont' want to do this for one instance of my class, so i don't want to specify the id because i don't know it and my result was a list. In fact, i would like to have the latest revision of all my object at a specific date :

date 1 :   create obj1 (revision0)   create obj2 (revision1)

date 2 :   update obj1 (revision2)

date 3 :   update obj1 (revision3)

date 4 :   update obj1 (revision4)

I would like my query return me my objects at the date "date3" so i would like to have the revision 3 and the revision 1.

Please help me. Thx

[adamw]

Well, did you try not specifying the id?

Or simply:

1. obtain the revision number corresponding to the id using AuditReader.revisionForDate

2. query for entities at that revision

If you get a revision for date3, and try reading obj1 and obj2 at that revision, you will get the data that you need.

Regards,

Adam Warski

[desol4tion]

But if i do like you tell me, i have to do X calls on envers where X is the number of differents data (id) on my audited table.

Exemple : if i have 4 distinct objects with id = 1,2,3 and 4,

                            i have to call envers one time to obtain the revision number of my data with id =1 at the date D, then i have to call it again to obtain the values i want. Same thing for my three other objects. So i have to do 8 calls to do this if i've understanded correctly what you tell me.

[adamw]

You only need to get the revision number once, for the specified date. Then you can read all of the entities in a single query, or in multiple queries.

So that's max 2 queries/Envers calls.

Adam

[desol4tion]

But it's difference instances of only one entity so if i put a static revision number, it'll return me only one instance, do you see what i want to do

In fact, i just want to do the same thing that :

select p from person

where p.date = ( select max(p2.date) from person p2

                                                  where p2.id = p.id

                                                  and p2.date <= dateParam )

with envers' queries but is it possible to do subquery with envers?

[drsaturno]

While my performance test, I realize that if we base our revision retrieve only in dates, revisions generated in less than 1ms apart could not be unique. I prefer to rely on revision number.

[adamw]

"if i put a static revision number, it'll return me only one instance, do you see what i want to do"

I don't think that's true. I think you also may misunderstand the revision system, or I misunderstand you.

If you have the following modifications:

rev1: insert person1 (p1a) (id = 1)

rev2: insert person2 (p2a) (id = 2)

rev3: update person2 (p2b)

rev4: update person1 (p1b)

Then, if you read person with id = 1 at revision 1, 2 or 3 you will get the p1a object. Similarly, if you read person with id = 2 at revision 4, you will get p2b. So if you have one revision number, you can get all the persons at that revision with one query.

Adam

[desol4tion]

I understand it but i don't see how to make this query :

if i do    

Number revisionMax = reader.getRevisionNumberForDate(dateEffetRecherche);  ------ For exemple 3

AuditQuery query = reader.createQuery().forRevisionsOfEntity(PersonEntity.class,false, true);

query.add(AuditEntity.revisionNumber().eq(revisionMax));  ----- we take the revision 3

query.add(AuditEntity.id().eq(1));      ---- Will singleResult be p1a ?

if i do

Number revisionMax = reader.getRevisionNumberForDate(dateEffetRecherche);  ------ For exemple 3

AuditQuery query = reader.createQuery().forRevisionsOfEntity(PersonEntity.class,false, true);

query.add(AuditEntity.revisionNumber().eq(revisionMax));  ----- we take the revision 3

query.add(AuditEntity.id().eq(2));      ---- singleResult will be p2b

Finally, i want p1a and p2b so i have to don't specify the id

Number revisionMax = reader.getRevisionNumberForDate(dateEffetRecherche);  ------ For exemple 3

AuditQuery query = reader.createQuery().forRevisionsOfEntity(PersonEntity.class,false, true);

query.add(AuditEntity.revisionNumber().eq(revisionMax));  ----- we take the revision 3

And i tested that, and the result is only the person at the revision 3 so i got p2b. I got the good result if i add i don't use :

query.add(AuditEntity.revisionNumber().eq(revisionMax));

but i use this :

query.add(AuditEntity.revisionNumber().in((1,3)));

[adamw]

I think you should be using the entities-at-revision query (reader.createQuery().forEntitiesAtRevision()), as you are interested in entities at a given point in time, not in the whole history of entities.

If you do:

AuditQuery query = reader.createQuery().forRevisionsOfEntity(PersonEntity.class,false, true);

query.add(AuditEntity.revisionNumber().eq(revisionMax));

You will get *only* the entities which were modified *exactly* at revision 3. Not entities which also existed at rev 3, but were not modified.

Adam

[desol4tion]

Thanks adam, it works pretty fine, it's exactly what i nedd to do, i just didn't know this function !