HQL Syntax Question

[Jeff]

I have the following sql query that I'd like to convert to an HQL query. The problem I'm having is that HQL doesn't like the 'on' keyword so I'm not sure how to perform the conversion. Any help would be greatly appreciated.

select *
from jukeboxes as j inner join jukebox_statuses as js on js.jukebox_id = j.id left join device_codes as dc on j.id = dc.device_id

[ideasculptor]

On Thu, May 31, 2012 at 5:19 PM, Jeff <[hidden email]> wrote:

I have the following sql query that I'd like to convert to an HQL query. The
problem I'm having is that HQL doesn't like the 'on' keyword so I'm not sure
how to perform the conversion. Any help would be greatly appreciated.

select *
from jukeboxes as j inner join jukebox_statuses as js on js.jukebox_id =
j.id left join device_codes as dc on j.id = dc.device_id

Don't think about HQL joining tables.  It queries your object model.  As such, it already knows how to join any two objects which are related because the mapping definition knows which column(s) act as foreign keys.  Assuming you are trying to load all jukeboxes and you want to eagerly load the status and code objects which are properties of Jukebox, your query would look something like this:

select j from Jukebox j fetch join j.status s fetch join j.code c 

(you don't actually need the 's' and 'c' aliases unless you plan to filter via a where clause or join to further relations via s.property or c.property)

All of this assumes your object model looks something like this:

public class Jukebox {

    JukeboxStatus status

    DeviceCode code

}

and that you have JukeboxStatus and DeviceCode mapped as objects in hibernate as well.

Bear in mind that if you have more than one status or code for a given jukebox and you do an eager fetch, you will get more than one entry for that jukebox in the resulting list, since it will return one row for each status/code combination it finds in the db.  I think you can do a 'select distinct j from jukebox fetch join j.status s fetch join j.code' to correct that problem, but I'm not sure, and it may not do the eager fetching when you combine with distinct.

Try reading the first sections of the HQL documentation for more info: http://docs.jboss.org/hibernate/orm/3.6/reference/en-US/html/queryhql.html

 

--
View this message in context: http://grails.1312388.n4.nabble.com/HQL-Query-Question-tp4629351.html
Sent from the Grails - user mailing list archive at Nabble.com.

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

   http://xircles.codehaus.org/manage_email

[Jeff]

Hi Samuel,

Unfortunately my object model doesn't look like the one you proposed due to the reasons I mentioned in a previous post.

Having said that, I know I can join the jukebox and jukebox_status tables using 'from Jukebox j, JukeboxStatus js where j.id = js.jukeboxId' so I'm thinking there has to be a way to combine that with a left join on the device_code table.