Hi I have a SQL query I am trying to translate to HQL, and having difficulty. It's not too complex, but has a couple of outer joins to the same table, with correlated subqueries:



To give some more info on the database structure...the Document is my main table. This has a few columns, but more importantly has a one-to-many relationship to Document_Metadata. Finally this has a many-to-one relationship with the Metadata_Type table, which defines the extra metadata that can be applied to documents. This is an extensible structure where "extra" information about Documents can be added without the need to keep adding new columns to the Document table. So if a new piece of document data needs to be stored, we don't add a column to the table, just a new row to the Metadata_Type table, then the relevant data itself in the Document_Metadata table.

Here's the actual structures...table names and then columns in brackets:

Document(Id, Name, Filename, ...many more...)

Document_Metadata(DocumentMetadataId, MetadataTypeId, DocumentId, Value)

Metadata_Type(Id, Name)

Here's some sample data:

Document
1, "first document", "one.txt"
2, "second document", "two.txt"

Document_Metadata
1, 1, 1, "Smith, Paul"
2, 1, 2, "2010-02-01"
3, 1, 1, "Brown, John"

Metadata_Type
1, "Author"
2, "Publish Date"

Please note that the Document_Metadata is optional. For example, document #2 does not have a Publish Date. Hence the use of left outer join in the query.

I'd prefer help in HQL rather than Criteria API, as I have seen some criteria examples with detached criteria and it seems very hard to follow. My real query is actually bigger than the one I have given above, I took some out for brevity!

Many thanks,

Paul