I'm having problems porting an SQL query over to HQL.
I'm using Hibernate 2.1, with Postgresql 7.4.2.

I have the following data objects that I'm generating the database from:

/*
* @hibernate.class table="bsFileInformation"
*/
public class FileInformation implements Serializable
{
/*
* @hibernate.id
* generator-class="native"
* column="id"
* unsaved-value="-1"
* type="long"
*/
public long getId() {return id;}
public void setId(long id) {this.id = id;}
private long id = -1;

/*
* @hibernate.property
* name="path"
* type="java.lang.String"
* column="path"
* not-null="true"
* length="250"
*/
public String getPath() {return path;}
public void setPath(String path){this.path = path;}
private String path = null;
}

/*
* @hibernate.class table="FileData"
*/
public class FileData implements Serializable
{
/*
* @hibernate.id
* generator-class="native"
* column="id"
* unsaved-value="-1"
* type="long"
*/
public long getId() {return id;}
public void setId(long id) {this.id = id;}
private long id = -1;

/*
* @hibernate.property
* name="versionDate"
* type="java.util.Date"
* column="versionDate"
* not-null="true"
*/
public Date getVersionDate() {return versionDate;}
public void setVersionDate(Date versionDate) {this.versionDate = versionDate;}
private Date versionDate = null;

/*
* @hibernate.many-to-one
* class="org.test.FileInformation"
* column="fileInformationNo"
* foreign-key="id"
*/
public FileInformation getFileInformation() {return fileInformation;}
public void setFileInformation(FileInformation fileInformation) {this.fileInformation = fileInformation;}
private FileInformation fileInformation = null;
}

/*
* @hibernate.class table="MetaData"
*/
public class MetaData implements Serializable
{
/*
* @hibernate.id
* generator-class="native"
* column="id"
* unsaved-value="-1"
* type="long"
*/
public long getId() {return id;}
public void setId(long id) {this.id = id;}
private long id = -1;

/*
* @hibernate.property
* name="versionDate"
* type="java.util.Date"
* column="versionDate"
* not-null="true"
*/
public Date getVersionDate() {return versionDate;}
public void setVersionDate(Date versionDate) {this.versionDate = versionDate;}
private Date versionDate = null;

/*
* @hibernate.many-to-one
* class="org.test.FileInformation"
* column="fileInformationNo"
* foreign-key="id"
*/
public FileInformation getFileInformation() {return fileInformation;}
public void setFileInformation(FileInformation fileInformation) {this.fileInformation = fileInformation;}
private FileInformation fileInformation = null;
}

After generating the HQL files using the xdoclet.modules.hibernate.HibernateDocletTask and then creating the database using net.sf.hibernate.tool.hbm2ddl.SchemaExportTask, I wrote a plain SQL statement to get the information I wanted.
The following SQL statement gets the file path, the latest file data version date, and the latest meta data version date, but not exceeding a specified date (in this case the current timestamp in postgresql for testing).
This query gets the correct data.

select
fileInfo.path,
fileData.versionDate as fileVersionDate,
metaData.versionDate as metaVersionDate,
from
FileData as fileData,
MetaData as metaData,
FileInformation as fileInfo,
(
select
fileData.fileInformationNo,
max(fileData.versionDate)
from
FileData as fileData
where
fileData.versionDate <= CURRENT_TIMESTAMP
group by
fileData.fileInformationNo
) as fileMax,
(
select
metaData.fileInformationNo,
max(metaData.versionDate)
from
MetaData as metaData
where
metaData.versionDate <= CURRENT_TIMESTAMP
group by
metaData.fileInformationNo
) as metaMax
where
fileData.fileInformationNo = fileInfo.id and
metaData.fileInformationNo = fileInfo.id and
fileData.fileInformationNo = fileMax.fileInformationNo and
fileData.versionDate = fileMax.max and
metaData.fileInformationNo = metaMax.fileInformationNo and
metaData.versionDate = metaMax.max


I've tried to convert this over to HQL so that I can get the same effect (actually, in this case I'm trying to get the full objects back), but it doesn't seem to work.

public class FileQueries
{
private static final SessionFactory sessionFactory;

static {
Configuration config = new Configuration();
config.configure();
sessionFactory = config.buildSessionFactory();
}

private static final String listQuery =
"select" +
" fileInfo," +
" fileData," +
" metaData" +
" from" +
" FileData as fileData," +
" MetaData as metaData," +
" FileInformation as fileInfo," +
" (" +
" select" +
" fileData.fileInformationNo," +
" max(fileData.versionDate)" +
" from" +
" FileData as fileData" +
" where" +
" fileData.versionDate <= :referenceDate" +
" group by" +
" fileData.fileInformationNo" +
" ) as fileMax," +
" (" +
" select" +
" metaData.fileInformationNo," +
" max(metaData.versionDate)" +
" from" +
" MetaData as metaData" +
" where" +
" metaData.versionDate <= :referenceDate" +
" group by" +
" metaData.fileInformationNo" +
" ) as metaMax" +
" where" +
" fileData.fileInformationNo = fileInfo.id and" +
" metaData.fileInformationNo = fileInfo.id and" +
" fileData.fileInformationNo = fileMax.fileInformationNo and" +
" fileData.versionDate = fileMax.max and" +
" metaData.fileInformationNo = metaMax.fileInformationNo and" +
" metaData.versionDate = metaMax.max";

public static void makeList(Date referenceDate) throws HibernateException
{
Session s = sessionFactory.openSession();
Query query = s.createQuery(listQuery);
query.setDate("referenceDate", referenceDate);
List result = query.list();
s.close();
return result;
}


Running the preceeding results in an exception when it tries to run "List result = query.list();"
net.sf.hibernate.QueryException: in expected: select [select fileInfo, fileData, metaData from org.test.FileData as fileData, org.test.MetaData as metaData, org.test.FileInformation as fileInfo, ( select fileData.fileInformationNo, max(fileData.versionDate) from org.test.FileData as fileData where fileData.versionDate <= :referenceDate group by fileData.fileInformationNo ) as fileMax, ( select metaData.fileInformationNo, max(metaData.versionDate) from org.test.MetaData as metaData where metaData.versionDate <= :referenceDate group by metaData.fileInformationNo ) as metaMax where fileData.fileInformationNo = fileInfo.id and metaData.fileInformationNo = fileInfo.id and fileData.fileInformationNo = fileMax.fileInformationNo and fileData.versionDate = fileMax.max and metaData.fileInformationNo = metaMax.fileInformationNo and metaData.versionDate = metaMax.max]
at net.sf.hibernate.hql.FromParser.token(FromParser.java:102)
at net.sf.hibernate.hql.ClauseParser.token(ClauseParser.java:87)
at net.sf.hibernate.hql.PreprocessingParser.token(PreprocessingParser.java:123)
at net.sf.hibernate.hql.ParserHelper.parse(ParserHelper.java:29)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:149)
at net.sf.hibernate.hql.QueryTranslator.compile(QueryTranslator.java:138)
at net.sf.hibernate.impl.SessionFactoryImpl.getQuery(SessionFactoryImpl.java:294)
at net.sf.hibernate.impl.SessionImpl.getQueries(SessionImpl.java:1562)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1533)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:39)
at org.test.FileQueries.makeList(FileQueries.java:59)
at org.test.main(Main.java:34)

I've tried a few variations on this, including trying to get individual fields rather than the full objects, but it only resulted in more obscure errors =(
The hibernate docs seem to only cover single table or self-referential table issues for the most part so I'm really stuck here. Any ideas on how to make this work?

Hibernate knows nothing about the class org.test.FileData.

[quote="gavin"]Hibernate knows nothing about the class org.test.FileData.[/quote]


Uhh.. okaaaaaay...

So, like, any hints on why it knows nothing about the class org.test.FileData?

You didn't add the mapping to your Configuration

ooops sorry, different problem.

The problem in your case is that you have tried to write a subselect in the FROM clause, which is not supported.

The hibernate.cfg.xml? It's configured like this:

<?xml version='1.0' encoding='utf-8'?>
<!DOCTYPE hibernate-configuration
PUBLIC "-//Hibernate/Hibernate Configuration DTD//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-2.0.dtd">
<hibernate-configuration>
<session-factory>
<property name="show_sql">true</property>
<property name="dialect">net.sf.hibernate.dialect.PostgreSQLDialect</property>
<property name="hibernate.query.substitutions">true 1, false 0</property>
<property name="hibernate.connection.driver_class">org.postgresql.Driver</property>
<property name="hibernate.connection.url">jdbc:postgresql://127.0.0.1/testing?useUnicode=true</property>
<property name="hibernate.connection.username">testing</property>
<property name="hibernate.connection.password">testing</property>
<!-- Mapping files -->
<mapping resource="org/test/FileData.hbm.xml"/>
<mapping resource="org/test/FileInformation.hbm.xml"/>
<mapping resource="org/test/MetaData.hbm.xml"/>
</session-factory>
</hibernate-configuration>

The hbm files were created by the xdoclet task:

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="org.test.FileInformation"
table="FileInformation"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="id"
type="long"
unsaved-value="-1"
>
<generator class="native">
</generator>
</id>

<property
name="path"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="path"
length="250"
not-null="true"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-FileInformation.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>



<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="org.test.FileData"
table="FileData"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="id"
type="long"
unsaved-value="-1"
>
<generator class="native">
</generator>
</id>

<many-to-one
name="fileInformation"
class="org.test.FileInformation"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
foreign-key="id"
column="fileInformationNo"
/>

<property
name="versionDate"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="versionDate"
not-null="true"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-FileData.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>



<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class
name="org.test.MetaData"
table="MetaData"
dynamic-update="false"
dynamic-insert="false"
>

<id
name="id"
column="id"
type="long"
unsaved-value="-1"
>
<generator class="native">
</generator>
</id>

<many-to-one
name="fileInformation"
class="org.test.FileInformation"
cascade="none"
outer-join="auto"
update="true"
insert="true"
access="property"
foreign-key="id"
column="fileInformationNo"
/>

<property
name="versionDate"
type="java.util.Date"
update="true"
insert="true"
access="property"
column="versionDate"
not-null="true"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-MetaData.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>


If hibernate knows nothing about org.test.FileData, then why did it replace FileData with org.test.FileData in the query I passed to it?

[quote="gavin"]ooops sorry, different problem.

The problem in your case is that you have tried to write a subselect in the FROM clause, which is not supported.[/quote]


Ahh ok. Do you know of any other way to get this information?

Can you do something like:

Depending upon the semantics of your query, you might want to use "in", instead of "=".

Ah that worked perfectly. Thanks!

I used = since I wanted only the FileData and MetaData with the most recent date that was less than or equal to the date given in the function call.