NHibernate specific mapping ("select using grand-parent")

Question

I have such domain structure (example, pseudo code):

class Room 
{
  Id: long
  Shelves: list of Shelf
}

class Shelf 
{
  Id: long
  Room: Room class (room_id column)
  Books: list of Book
}

class Book 
{
    Id: long (id column)
    Shelf: Shelf class (shelf_id column)
    Room: Room class (room_id column)
    Title: string (title column)
}

Every collection is lazy. I'd like to know is there a way to fetch all books for a room (without subselect) when I access a Shelf's Books property in lazy manner.

When I get Room, nhibernate fetches only room (select * from rooms r where ...), when I access Shelves property, nhibernate fetches all shelves for this room (select * from shelves s where s.room_id = X), when I access Books property it loads books for the only one shelf (what is normal in general), but...

In my case if I access Book, there is high probability that I will work with other books of this room too, so if I touch book I want to preload all books for this room and put them on theirs shelves.

The most closest feature of nhibernate is fetch='subselect'. But there is unnecessary select in my case because I can fetch books for this room by room_id: select * from books b where b.room_id = x.

Is there a way to do this?

Thanks!

Answer 1

I've found a more elegant solution. It turns out I can define sql-query for a bag/map...

It looks like:

<hibernate-mapping...>
    <class name="Shelf">
        <many-to-one name="Room" column="room_id"... />

        <bag name="Books">
            ...

            <loader query-ref="ShelvesQuery" />
       </bag>
    </class>

    <sql-query name="ShelvesQuery">
        <load-collection alias="book" role="Shelf.Books" />

        <![CDATA[
          select b.id
                 b.room_id,
                 b.shelf_id,
                 b.title
            from books b
           where b.room_id = :room_id
       ]]>
    </sql-query>
</hibernate-mapping>

It works like a charm!