13 Replies Latest reply on Sep 9, 2011 8:54 AM by Stephane Fernandez

    Retrieve by date

    Amar Singh Newbie

      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.

        • 1. Re: Retrieve by date
          Amar Singh Newbie

          Please note the version I am using is:

          Envers 1.1.0 compatible for Hibernate 3.2.6

          • 2. Re: Retrieve by date
            Adam Warski Master

            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

            • 3. Re: Retrieve by date
              Amar Singh Newbie

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

              • 4. Re: Retrieve by date
                Stephane Fernandez Newbie

                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

                • 5. Re: Retrieve by date
                  Adam Warski Master

                  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

                  • 6. Re: Retrieve by date
                    Stephane Fernandez Newbie

                    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.

                    • 7. Re: Retrieve by date
                      Adam Warski Master

                      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

                      • 8. Re: Retrieve by date
                        Stephane Fernandez Newbie

                        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?

                        • 9. Re: Retrieve by date
                          Adriano Saturno Newbie

                          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.

                          • 10. Re: Retrieve by date
                            Adam Warski Master

                            "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

                            • 11. Re: Retrieve by date
                              Stephane Fernandez Newbie

                              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)));

                              • 12. Re: Retrieve by date
                                Adam Warski Master

                                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

                                • 13. Re: Retrieve by date
                                  Stephane Fernandez Newbie

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