Hi

I was wondering if anyone could help me with a problem I am having mapping relationships in my legacy database. All of the data in the database is time-dependent and has date/time columns like valid_from and valid_to. The primary key is comprised of a single identifier column and these date fields so if I want to get the value for one for the current date I would need to query it like this:

select * from table_name where id=:id and valid_from <= current_time() and valid_to > current_time()

If I am accessing a row directly this is fine and I can use a predefined query to get the result for my id value.

However, I need to set up a parent/child relationship which includes this kind of lookup. Does anyone know how I can define a property mapping where I can specify that a child attribute (for example my valid_from column above) is less than or equal to one of the parent attributes or at least less than or equal to the current_time() value? Is my best bet to use a <formula> element and put the whole query in there or could I map this so that hibernate can still return the parent and child with a single query using an outer join?

Any thoughts or suggestions would be much appreciated.

Thanks

ps. I have tried various mappings to get this right and nothing has worked so far so I don't really have any example stuff to post at the moment but I can provice more details if this would be helpful.