Win a copy of The Business Blockchain this week in the Cloud forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help with HQL statement

 
David Button
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi all-

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!
 
Nikita Protsenko
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.

Best regards,
Nikita Protsenko
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic