-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Historical records in same table as active records
PostPosted: Mon Jan 26, 2009 1:08 pm 
Newbie

Joined: Mon Jan 26, 2009 12:08 pm
Posts: 2
Location: Denmark
Hi guys,

I´ve run into some difficulties with historical records in the same table as active records. It seems to me that I have been thru every point in the documentation in search for a sollution; filters, @Where, and @Loader without results. (@Loader because a) I don´t like the concept of hardcoding more SQL than necessary, and b) @Loader doesn´t work when other entities is joined against my entity)

Let me just describe you the problem:

My table contains the following columns:
  • id, int(10)
  • validFrom, datetime
  • validTo, datetime
  • rowStatus, enum('active','inactive','deleted')active
  • data, varchar(120)


The table contains all changes made to the entity, which is illustrated by the following rows:

Code:
id           validFrom validTo rowStatus
1 2009-01-26 16:31:09    NULL    active   
1 2009-01-26 16:32:05    NULL    active


What I´m trying to achive is that Hibernate generates SQL which would look like this:

Code:
SELECT *
FROM entity_table o
WHERE o.id = 1
AND o.validFrom = (
    SELECT MAX(i.validFrom)
    FROM entity_table i
    WHERE o.id = i.id
    AND i.validFrom <= NOW()
    AND (
        i.validTo >= NOW()
     OR i.validTo IS NULL
    )
)
AND o.rowStatus = 'active'


The problem is that I don´t know the alias of the outer table which makes it impossible to preform the correct subselect. I can get Hibernate to return me all rows regardless of the latest validFrom (without subselect) and the globally newest row (without o.id = i.id) - but I simply cant get it right :-(

Anyone up for the fight? Thanks in advance...

-- Brian


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.