HQL join problem

[Prasath Premkumar]

Hi all,

I have a table named doc_type and another one named release. In doc_type.hbm.xml i have defined a many-to-one mapping for release like this:

 <many-to-one name="release" class = "com.openreach.portal.dto.Release" lazy="false" column="RELEASE_ID" unique="true"/>
           

note that DocType can be without release too.

I wrote a HQL like this for a scenario where i needed to get all the doctypes (with or without release) for a common name ordered by release.releaseOrder and issueDate.

  from DocType as dt where dt.commonName=? and dt.documentStatus = 1 order by dt.release.releaseOrder desc, dt.issuedDate desc  

But it didnt return DocTypes which dont have release. But when i removed

 dt.release.releaseOrder desc,  

from the order by clause, it returned all the doctypes with or without release object. (which is the expected behavior). So i found out that since i'm using an attribute of the Release object in HQL, when Release is null in DocType, it is not returning that record.

So i thought i can to a left join and wrote a HQL like this:

 from DocType as dt left join dt.release where dt.commonName=? and dt.documentStatus = 1 order by dt.release.releaseOrder desc, dt.issuedDate desc

which returned everything but instead of returning a List, it resulted in List<List> something like that....

What is the possible HQL for this scenario???

Thanks in advance.

[Prasath Premkumar]

Hi,

I was able to fix that issue. Thanks anyway.

If anyone wants to know what i did:

 select dt from DocType dt left join dt.release r where dt.commonName=? and dt.documentStatus = 1 order by r.releaseOrder desc, dv.issuedDate desc  

This HQL does what i needed.

Thanks.