I have a database that is being used as a sort of version control system. That is, instead of ever updating any rows, I add a new row with the same information. Each row also contains a version column that is a date timestamp, so the only difference is the new row will have a more recent timestamp. Each product has an ID that does not change. Each ID will have multiple "versions" and I would like to return the most recent "version" of each ID.
What I'm having trouble with is writing an efficient hibernate query to return the latest version of these rows. For the sake of example, these are rows in a table called Product, the timestamped column is version. There are multiple versions of multiple products in the table. So there may be multiple versions (rows) of ProductA, multiple versions of ProductB, etc. And I would like to grab the latest version of each.
Can I do this in just a single hibernate query?
I think something like this should theoretically work, but I don't know how to implement it in HQL
That may or may not be on the right track, but can anybody help me achieve this in hibernate? Thanks!
To do this, you'll have to use sub-queries. If you connect your Products via the "name" column, then your query should look like that:
I would also consider redesigning a data structure to use one-to-many association to keep your "versions" on the "many" end. This way you can query them more efficiently - in fact, all you need is to order them on the "timestamp" column in descending order and fetch the first one.
I'm all tasted up for a BLT! This tiny ad wants a monte cristo!