Populating a bag with a custom SQL SELECT statement?

itakenocrud · **Joined:** Thu Mar 12, 2009 5:31 pm **Posts:** 16

Anyone have any sample code for populating a bag with a custom SQL
SELECT statement? I need to be able to run a query using the object's
id and it should return a list of another type of object.

The mapping code below demonstrates sorta what I need to do but this
doesn't work.

Mapping Code:

Code:

<class name="Book" table="Devices">
   <id name="Id" type="Int32" column="Id" access="property">
     <generator class="identity">
       <param name="table">Books</param>
       <param name="column">Id</param>
     </generator>
   </id>
   <bag name="PagesInBook" inverse="true">
     <key></key>
     <one-to-many class="Pages"/>
     <loader query-ref="customLoader"/>
   </bag>
 </class>
<class name="Page" table="Pages">
   <id name="Id" type="Int32" column="Id" access="property">
     <generator class="identity">
       <param name="table">Pages</param>
       <param name="column">Id</param>
     </generator>
   <property name ="BookId" column="fkBookId"/>
   </id>
</class>
<sql-query name="customLoader">
   <load-collection alias="dev" role="Page"/>
   <![CDATA[SELECT * FROM PAGES WHERE Pages.fkbookId= :id]]>
 </sql-query>

wolli · **Posted:** Fri Mar 13, 2009 3:37 am

Try this:

Code:

<sql-query name="customLoader"> 
   <load-collection alias="p" role="Book.PagesInBook"/> 
    SELECT {p.*} FROM PAGES p WHERE p.fkbookId= :id
 </sql-query>

itakenocrud · **Joined:** Thu Mar 12, 2009 5:31 pm **Posts:** 16

I have managed to get nhibnerate to load the information as a sql query. But this is very much against the repository pattern I am using. For some strange reason (a bug?) nHibernate is attempting to load the pages lazily, even though, I have marked lazy load as false.

I tried what you suggested, but it seemed to me there was a problem with the Id or something, because the database kept giving me an error about the Id used.

Code:

<class name="Books" table="Books">
    <id name="Id" type="Int32" column="Id" access="property">
      <generator class="identity">
        <param name="table">Books</param>
        <param name="column">Id</param>
      </generator>
    </id>
<bag name="PagesInBook" inverse="true"  cascade="save-update" lazy="false">
      <key column="Id"></key>
      <one-to-many class="Page" />
      <loader query-ref="GetPagesInbook" />
    </bag>
  </class>
 <sql-query name="GetPagesInbook">
   <!--<load-collection alias="dev1" role="Book.PagesInBook" />-->
   <return alias="dev" class="Page"/>
   <![CDATA[
   select
   Id AS {dev.Id},
   Text AS {dev.Text},
   from DevicesInGroup
   ]]>
 </sql-query>

itakenocrud · **Joined:** Thu Mar 12, 2009 5:31 pm **Posts:** 16

I finally figured it out. There is no solution. nHibernate doesn't support populating an class from multiple tables. Time to load up the Entity Framework. :)

wolli · **Posted:** Tue Mar 17, 2009 2:42 am

Sure about that ? Thta's what these custom sql are for. Alternativly you can try to create a view and map the view.

What exactly have you tried so far and what does the error exactly say ? Until now you never mentioned that you want to populate the entry from multiple table. Can cou post the tables and the sql query that you need ?

itakenocrud · **Joined:** Thu Mar 12, 2009 5:31 pm **Posts:** 16

I have a class called Book that is mapped to a table called [Books]. I have a separate table called [Pages] that is mapped to a Page class. I need the Book class to have a List<Page> property that is populated using a custom native SQL query. I need the Page class to be populated from the table and the SQL query is not a simple one-to-many relationship. A native SQL query is mandatory to populate this.

As stated previously, this is not possible in nHibernate, because you can't populate a class using a custom SQL statement AND a have a table reference. I would either need to create 2 "Page" classes one to populate from the table and another to populate with the query. These is very much against best practices.

Take a look at these nHibernate links I have found:

In this one, the object that is being populated by the stored procedure doesn't reference a table:
- http://ayende.com/Blog/2006/09/18/Using ... dures.aspx

He doesn't give a class table in this topic:
- http://forum.hibernate.org/viewtopic.php?p=2376446

"what if you don't have a table like "Employment" to return, and you do want to return a dataset that doesn't map to any table?
I played with nHibernate a litte bit and find a way to circumvent this limitation. What you need to do is create an empty table with columns maps to the dataset you want to return. Create its hbm mapping and C# domain model class too. You don't have insert any data into this table. Then nHibernate will be able to call the stored procedure and return the dataset you want. The downside of this approach is you don't want to create and maintain a lot of empty tables to just make nHibernate happy."
- http://www.dalun.com/blogs/08.29.2007.htm

And if I managed to get all of this to work, I couldn't use a stored procedure.
"Notice that stored procedures currently only return scalars and entities. <return-join> and <load-collection> are not supported."
- http://nhforge.org/doc/nh/en/index.html

I have played with my code for at least 15 hours trying to get this to work and nothing I have found contracts this assumption. You can only populate a class either from 1 sql query OR a sql table. You can't use both.

nHibernate needs to seriously fix its documentation. The documentation page was of little or no help, because it fails to provide enough code and explanation to make it a viable resource. The only way a person can "learn" how to use nHibernate is by jumping from blog to blog, piecing bit by bit together until something is slapped together.

itakenocrud · **Joined:** Thu Mar 12, 2009 5:31 pm **Posts:** 16

Here is a bigger picture of what I was trying to do. I left out a few details that ended up being important. Before I neglected to specify that the Page class also referenced the [Pages] Table.

Code:

<class name="Book" table="Books">
    <id name="Id" type="Int32" column="Id" access="property">
      <generator class="identity">
        <param name="table">Books</param>
        <param name="column">Id</param>
      </generator>
    </id>
<bag name="PagesInBook" inverse="true"  cascade="save-update" lazy="false">
      <key column="Id"></key>
      <one-to-many class="Page" />
      <loader query-ref="GetPagesInbook" />
    </bag>
</class>

<class name="Page" table="Pages">
    <id name="Id" type="Int32" column="Id" access="property">
      <generator class="identity">
        <param name="table">Pages</param>
        <param name="column">Id</param>
      </generator>
    </id>
    <property name="text" column="Text" access="property" />
</class>

<sql-query name="GetPagesInbook">
   <!--<load-collection alias="dev1" role="Book.PagesInBook" />-->
   <return alias="dev" class="Page"/>
   <![CDATA[
   select
   Id AS {dev.Id},
   Text AS {dev.Text},
   from DevicesInGroup
   ]]>
 </sql-query>

wolli · **Posted:** Tue Mar 17, 2009 10:19 am

There's still something mising in the picture. How are books referenced in DevicesInGroup and what's the relationship between books/pages and DevicesInGroup ?

select
Id AS {dev.Id},
Text AS {dev.Text},
from DevicesInGroup
where BookId = ?

There has to be some kind of foreign key to find the pages for a book.

itakenocrud · **Joined:** Thu Mar 12, 2009 5:31 pm **Posts:** 16

Database scheme:
The books are stored in a tree. Where books have parent books and these groups of books forms collections. I am using a SQL query to find all of the pages in a book with multiple books inside. Pages are linked to books and books are linked to other books. So to find pages in a book, I need to find the pages in that book plus the pages in the sub-books.

If you really want, I could post some SQL table scripts to explain it better.

Please note that I updated my "Sources" post.

The query I have is quite large so I simplified the query for the post.

wolli · **Posted:** Tue Mar 17, 2009 10:52 am

I can't help you withou mote information. Let's start with the (full) SQL query you want to use for retrieving the pages of a book.

itakenocrud · **Joined:** Thu Mar 12, 2009 5:31 pm **Posts:** 16

Picture of the basic scheme:

SQL 2005 Query:

Code:

with Hierarchy (Id, ParentId, Level)
   as
   (
   select Id, ParentId, 0 as Level
   from Books e
   where e.Id = :id
   union all
   select e.Id, e.ParentId, eh.Level + 1
   from Books e
   inner join Hierarchy eh 
   on e.ParentId= eh.Id
   ),
   SubBooks(Id)
   as
   (
   select Id 
   from Hierarchy 
   where Level > 0
   ),
   AllPagesInBook(Id, Text)
   as
   (
   select Pages.* 
   from PagesInBooks
   inner join Pages 
   on (Pages.Id = PagesInBooks.fkPageId)
   inner join SubBooks 
   on (SubBooks.Id = PagesInBooks.fkBookId)
   )
   select *
   from AllPagesInBook

wolli · **Posted:** Tue Mar 17, 2009 11:59 am

OK, now we're getting somewhere ;-). Perhaps, this form of query can't be used directly for custom loading.
Try this:

Code:

<sql-query name="GetPagesInbook"> 
   <load-collection alias="dev" role="Book.PagesInBook" />
with Hierarchy (Id, ParentId, Level) 
   as 
   ( 
   select Id, ParentId, 0 as Level 
   from Books e 
   where e.Id = :id 
   union all 
   select e.Id, e.ParentId, eh.Level + 1 
   from Books e 
   inner join Hierarchy eh 
   on e.ParentId= eh.Id 
   ), 
   SubBooks(Id) 
   as 
   ( 
   select Id 
   from Hierarchy 
   where Level > 0 
   ), 
   AllPagesInBook(Id, Text) 
   as 
   ( 
   select Pages.* 
   from PagesInBooks 
   inner join Pages 
   on (Pages.Id = PagesInBooks.fkPageId) 
   inner join SubBooks 
   on (SubBooks.Id = PagesInBooks.fkBookId) 
   ) 
   select Id as {dev.Id}, Text as {dev.Text}
   from AllPagesInBook where Id = :id
 </sql-query> 

[/code]

Post the error if you get one. Maybe you have to wrap the query in a stored procedure, but then you run into some other problems, because "load-collection" is currently not supported for stored procedures. In that case you'll probably have to write a query that returns books and pages and fill these values into the appropriate entities. Last example before chapter 15.

http://www.nhforge.org/doc/nh/en/index.html#querysql-load

itakenocrud · **Joined:** Thu Mar 12, 2009 5:31 pm **Posts:** 16

Code:

   select Id as {dev.Id}, Text as {dev.Text}
   from AllPagesInBook where Id = :id 

If you look in the Heirarchy section of the query, I am already matching the top level book Id with ":id".

I am able to call the query as a <sql-query> from a repository class and have it return the list of pages from a given book id.

After that stage, I attempted to map pages in book to the Book property and I went through a range of errors.

Eventually I was able to retrieve a book object without any errors. But it wasn't filling up the List of Pages. It had a count of 0.

wolli · **Posted:** Tue Mar 17, 2009 12:42 pm

Quote:

If you look in the Heirarchy section of the query, I am already matching the top level book Id with ":id".

Sorry, missed that one. What error(s) do you get when you specifiy the collection load in this way ? As a last try you can replace ":id" with "?".

itakenocrud · **Joined:** Thu Mar 12, 2009 5:31 pm **Posts:** 16

:id and ? seem to be interchange-able.

If you take a look at my test code:
Sample code:

Code:

Repository<Book> bookRep= new Repository<Book>();
        Book selectedBook = bookRep.GetByProperty<Book>("Id", 2592);
        List<IAlfredDevice> pages = selectedBook .UniCastDevices.ToList<Page>();
        List<Page> pages2 = bookRep.SQlQuery<Page>("GetPagesInbook","id",2592);

Snippets of Repository Class:

Code:

public T GetByProperty<T>(string property, object value)
        {
            using (ISession session = NHibernateHelper<T>.OpenSession())
            using (ITransaction transaction = session.BeginTransaction())
            {
                T val = (T)session.CreateCriteria(typeof(T)).Add(Restrictions.Eq(property, value)).SetMaxResults(1).UniqueResult<T>();
                transaction.Commit();
                return val;
            }
        }

Code:

public List<S> SQlQuery<S>(string query,string param1,int value1)
        {
            using (ISession session = NHibernateHelper<T>.OpenSession())
            using (ITransaction transaction = session.BeginTransaction())
            {
                var list = session.GetNamedQuery(query).SetParameter(param1,value1).List<S>();
                transaction.Commit();
                return (List<S>)list;
            }
        }

Code:

OUTPUT:
pages.Count = 0;
pages2.Count = 4;