Can someone clarify something for me? For some reason I had the impression that the Filter conditions were an HQL fragment yet it would appear that as I look at some more examples that it would be a SQL fragment? However, I believe I have seen some conflicting examples. For example, in the Reference doc the fragment references the column names rather than the property names suggesting SQL however I'm sure I've seen some that do things such as this.propertyName = 'bleh'.

If it is actually a SQL fragment then would the following be doable? I've got an instance where I may have multiple versions of a record due to an effective date strategy. The multiple records will have the same business key and what I want to find is the most current effective record.

The structure is essentially I have a 'header' table that contains the static data, i.e. Primary Key and other things that don't change over time.

Hanging off the 'header' is a version table that contains it's own PK, a FK back to the header table, a date range and any temporal properties.

Our code is doing filtering like this on the java side however, I'm thinking we'll need to filter on the db side since if a property HAD a value of 'X' at one point and is now 'Y' I don't want to return the record if someone searches on 'X'. i.e. I want to find a header record where a temporal property is a certain value at a certain point in time.

If I were to write the SQL to handle the version filtering it would look something like this:



My next step will be to shoehorn hib 3 into the mix and try it out but if someone has a heads up that it may or may not work it might save me some head scratching :)

Also, I'm assuming that if I do this subquery then I'll need to do a filter for every header/version table combo that I have? It would be fantastic if I could define a generic filter for all of my combos as my filter criteria will be the same across the board, just the names of the tables will change.

Well, we've finally got around to the shoehorning and answered some of our own questions ;)

However, to help any other poor souls out there...


It IS indeed a SQL fragment. Either I've seen invalid examples or I'm on dope (50/50 chance)

Subqueries DO work however you can't use the table name as a parameter since it translates the filter into a PreparedStatement and the PS doesn't support that. (consider maybe something that would do simple String substitution?)

Either way, it looks like we're going to try to get around the dynamic table name 'issue' by some Xdoclet magic since we'll know the table name at xml generation time.

Again the reason we wanted to do this was that we have a common db pattern for our temporal data and want to be apply to write generic filters as they'll essentially all be the same.