I'm pretty new with Hibernate/HQL. I have created a SQL query executed with a createSQLQuery() that does what I want but that doesn't feel right and it probably isn't that performant either (don't know if it uses 2nd level cache for example).

So here is what I want to do:
I want to select all active Channels (Channel.active) for a specific Customer (I have the id of the Customer) and then order them by Channel.location. There are two ways to find Channels for a Customer. One directly (from Customer) to Channels via a many-to-many and one indirectly (from Customer) via ChannelGroups (where ChannelGroups has to be active as well). How would I go about to create a query like this (preferably eager so all Channels are fetched in one select)?

I really don't have clue where to start...

Thanks,

/Marcus

Hibernate version: 2.1

Mapping documents:
<class name="xyz.Channel" table="channel" ...>
<cache usage="read-only"/>
<id name="id" column="channel_id" type="long">
<generator class="native"/>
</id>

<property name="active" type="boolean" column="active"/>
<property name="location" type="int" column="location"/>

...
</class>

<class name="xyz.ChannelGroup" table="channelgroup" ...>
<cache usage="read-only"/>
<id name="id" column="channelgroup_id" type="long">
<generator class="native"/>
</id>

<property name="active" type="boolean" column="active"/>

<set name="channels" table="channelgroup_channel"
lazy="true" cascade="save-update">
<key column="channelgroup_id"/>
<many-to-many class="xyz.Channel"
column="channel_id"/>
</set>

...
</class>

<class name="xyz.Customer" table="customer" ...>

<id name="id" column="customer_id" type="long">
<generator class="native"/>
</id>

<set name="channelGroups" table="customer_channelgroup"
lazy="true" cascade="none">
<key column="customer_id"/>
<many-to-many class="xyz.ChannelGroup"
column="channelgroup_id"/>
</set>

<set name="channels" table="customer_channel"
lazy="true" cascade="none">
<key column="customer_id"/>
<many-to-many class="xyz.Channel"
column="channel_id"/>
</set>

...

</class>


Name and version of the database you are using: MS SQL Server 2000 and MySQL 4.1