3 Replies Latest reply on Jan 29, 2009 4:05 AM by Adam Warski

    Finding all entities at a specific date...

    talios Newbie


      Is it possible to (easily) find all entities as they existed at a given date? I know I can "find revision for date" for a specific entity instance, but I've not seen anything for a more wider reaching search.

      i.e. "Given the date XXXX, find the most recent revision of each entity whose datefield > thedate".

      I did think of using:

      Number revisionForDate = reader.getRevisionNumberForDate(agreementDate);
      reader.createQuery().forEntitiesAtRevision(Agreement.class, revisionForDate).

      However, due to migrations/import/export, the revision date isn't relevant. Currently I'm thinking I'll need to drop to HQL or even SQL and do something along the lines of:

      SELECT * FROM smx3.agreement_versions WHERE (agreement_id, _revision) IN (
       SELECT agreement_id, MAX(_revision) AS _revision FROM smx3.agreement_versions
       WHERE from_date > '2006-07-21' AND thru_date < '2006-08-21' GROUP BY agreement_id);

      Is there a cleaner/better way?

        • 1. Re: Finding all entities at a specific date...
          Adam Warski Master


          revisions are global, so why won't "find revision for date" work? You don't specify any entity when there.

          I also don't understand what "revision date is not relevant" means :)


          • 2. Re: Finding all entities at a specific date...
            talios Newbie

            My bad - the problem of posting a question when you're knee deep in trying multiple ideas :)

            In this instance, as part of a migration process we're going to be generating lots revisions of entities in a short period of time. Each of the records have a from/thru date which defines the time period the record is relevant.

            Under normal circimstances where the revision date would be a "real" date (caused by a user, and not a mass data migration) I'd be able to use "get revision for date" and "find entitys for revision", however as all revisions will be within an hour of each other, I'm trying to look for a way to query this data without relying on revision date.

            One way I've thought I could get around this, is let my revision info listener rewrite the revision date as part of adding additional revision details as I migrate/replay the old transactions, which would let me "recreate" the history, then I could just use the api as you suggest. This is probably the easiest way ( the migration is a series of 60,000 or so HTTP POST/PUT requests into our REST server which starts off with a completely empty database ).

            • 3. Re: Finding all entities at a specific date...
              Adam Warski Master


              yes, either that, or with the new version of Envers, you can also put constraints on fields in the revision entity when constructing queries.

              Someting like: